Conditional dynamic dropdown contact from another sheet

Jon Barto

Hello All

Is this possible?

Is there a way to get conditional dropdowns?

Meaning

column 1       column 2     dropdown contact

trade cat       Market

Framer         Pittsburgh    dropdown would pull framer from Pittsburgh

All the contacts would on 1 Master sheet.  Master sheet would have corresponding Trad cat column and Market column.

On the construction schedule the Trade cat column would be already filled out as well as the market column.  

So each row would have a task on the construction schedule

Also new construction schedules would be created from a templated schedule.

See above example. So line 4 would see excavation phase and market of Pittsburgh and return only return sub contractor that has excavator and Pittsburgh in the sub sheet columns.

Paul Newcome

It sounds to me like you need an INDEX/COLLECT, but are you able to provide screenshots (sample data is fine) for reference?

Jon Barto

@Paul Newcome

Yes I can get that. But let me explain it maybe in a better way.

So If I am trying to select a subcontractor for a task (task is in row) Not sure if above screen shot showed up.(in previous comment)

But the subcontractor column would be pulling from a set sheet.... is there a way to reduce the contact list of that column of say 100 subcontractors down to 4 based off other columns such as (sub type column would be framer) then also by another column market area.

So when selecting a subcontractor for that row... the dropdown would be filtered down to only show "framers" in "said market"

Then for say another row it would show "electricians" in "said market"

Does that make sence?

Not sure if index/collect would work because sometimes there maybe 3 subs that would match that selection and we would then choose 1 of those who would get the job.

What I am trying to do is not have say someone pick an electrical company in a row that would require a framer.

Or have a subcontractor for the wrong market in wrong state selected for that job.

Paul Newcome

You won't be able to make the dropdown selections themselves dynamic, but we can put some things in place to help people choose.

You could use a JOIN/COLLECT with a CHAR(10) delimiter (line break) in a multi-select column to output a list of the appropriate people for selection in another column. Then use a hidden helper column with a formula to indicate if the selection made does not match one of the people listed in the JOIN/COLLECT column which in turn can drive conditional formatting to highlight the selection cell red.

Jon Barto

@Paul Newcome

Would you be able to email me at [email protected].

I would like to discuss further Smartsheet items that we are looking to outsource to have completed

Thanks