Automatic population based on answers

MelNZ
MelNZ
edited 11/24/22 in Formulas and Functions

Hi

I am new to Smart Sheet and are having a problem trying to set up an automatic population. I have a cell with multi dropdown, based on what a person selects the answers for two other cells on the same rows are predetermined.

If a meeting is X date then the date to confirm attendance is Y and agenda items are due Z date sort of thing. There are 7 meetings each with their own attendance and agenda items due dates. I will be setting reminders for each dates. I have tried the IF formula but cannot get it to do more than one situation. I have tried a workflow but as soon as I run a second workflow is over writes the pervious results.

Any and all suggestions greatly welcome 😀

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @MelNZ

    I hope you're well and safe!

    Not sure I follow.

    Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • MelNZ
    MelNZ
    edited 11/24/22

    Thank you for your message @AndreeStara. There are a number of meetings coming up, I need people to select which meeting (from dates listed as a text in a dropdown, lets call this column 2) they are referring too, if they select the February meeting then the two other deadlines (lets call them column 5 and 6) for that meeting I would like to automatically be updated/added in cells in the same row.

    I seem only to be able to do one date, if I try to include the other meeting dates using the IF formula I get an error message.

    If I use a different workflow for each meeting date it over writes the column as soon as a different date for the meeting is selected by the next person. If the first meeting selected was May, all the May dates are added to column 5 and 6 on that row, but as soon as the next person selects a different meeting date (lets say April) all the dates in column 5 and 6, regardless of what row they are in, are updated for April deadlines.

    I hope that makes sense.

    All the best,

    Mel

  • Hi @MelNZ

    I agree that it would be helpful to see some screen captures!

    It doesn't have to be your legitimate sheet - if you create a Copy with "save as new", you could delete out any sensitive information. We just need to see the columns you're working with to help with either a formula or a workflow set-up.

    Would you also be able to post the formula you tried that didn't work? And screen captures of the workflow? It sounds like you may be able to add in Condition Blocks to your workflow to filter what rows update, see: Condition Blocks: Filter What Your Automated Workflows Send

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • MelNZ
    MelNZ
    edited 12/05/22

    I am not sure how this will help but here is a screen shot.

    The Meeting Date column is a dropbox box with the dates listed to choose from. The Confirm by and Agenda Due date columns are set up for dates to be entered this way I can set reminders to be sent out.

    Condition blocks only gives me the option to have the date a person selected a meeting date entered, not the dates confirm or agenda papers are due by.

    When I have tried the IF formula I have only been successful with one IF option but there are 7 meeting dates so 7 confirm by and agenda due dates.

    Maybe I just need to manually update the Confirm by and Agenda Due once someone selects a meeting date so they get the reminders. I will include a status column with a tick box so once they have completed either task they don't get a second reminder.

    That should work right?

    Mel

  • Hi @MelNZ

    Thank you for clarifying! The set-up does help, and I think I can now picture the type of information you would be inputting in the cells.

    If I'm understanding you correctly, you have a multi-select cell that can have up to 7 options, which means that in each of your 3 date columns you want up to 7 dates to appear in each cell.

    Currently Date-type of columns in Smartsheet can only have a single date at a time (there isn't a multi-select date option) which means that you would need to define one "Confirm by" date and one "Due Date" based on what was selected.

    We can definitely help you write a formula to output a specific date, but there would need to be defined criteria for selecting one date over another. For example, if the Meeting Name has "Meeting 1" as one of the selections, then the Due Date is always 5 days later. Or, if the Meeting Name has "Meeting 5" selected, this one takes precedence and so it's due 2 days later. Does that make sense?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!