Conditional dynamic dropdown contact from another sheet

Jon Barto
Jon Barto ✭✭✭✭✭
edited 10/24/23 in Formulas and Functions

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.

Answers

  • Paul Newcome
    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?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jon Barto
    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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jon Barto
    Jon Barto ✭✭✭✭✭

    @Paul Newcome

    Would you be able to email me at jon@experienceparry.com.

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


    Thanks

  • David Jasven
    David Jasven ✭✭✭✭

    I have not yet read the entire thread, but in terms of Dynamic Drop down creation, I have created an Application for that due to the need. I am just talking about the dropdown. Ofcourse you can do formulas in sheets and take it further to reduce the results. For the Dynamic dropdown portion, see my video I put together for that.
    Also, Paul nice to meet you :-)

    I will continue reading the thread below and see if there is anything for me to add.

    Dynamic Smartsheet Drop downs….



  • David Jasven
    David Jasven ✭✭✭✭

    I love the JOIN / COLLECT Solution. Together with what I suggested above, @jon would solve the issue.


  • David Jasven
    David Jasven ✭✭✭✭

    Jon I hope you came right. Please check this thread for full solution.

    💗 If my response helped solve your problem, please consider marking it as Insightful, Vote Up, or Awesome. It helps the community find solutions more easily!

    SMARTSHEET EXPERT CONSULTANT & DEVELOPMENT

    David Jasven | Workflow and Business Process Consultant / CEO @ Must.do

    W: www.must.do | E: smartsheethelp@must.do | WhatsApp: +1 347 380-7697

    Feel comfortable to contact me for any help with Smartsheet, integrations, general business process advice, or anything else. I am more responsive on Whatsapp.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!