Populate Column From Another Sheet's Dropdown Menu

Muhammad
Muhammad āœ­āœ­āœ­
edited 02/15/24 in Formulas and Functions

Hi all!

I was wondering, is it possible to populate an entire column based on the dropdown menu from another sheet? I have one sheet where all tasks are listed, and are assigned to people via a dropdown menu. I am trying to create another sheet as a cross sheet reference that summarizes hours of every individual. What I would want is for the first column of this sheet to be populated by the values of the aforementioned dropdown menu. I could always do this manually, but ideally i want it to be as automated as possible, so in future if someone joins, we won't have to manually change this every time. Does anyone know if this or something like this would be possible? Thank you all in advance!


ex dropdown list: x, y, z

ex how I want the cross reference sheet to look


Col1 (name)

Row1: x

Row2: y

Row3: z

Best Answer

  • Paul Newcome
    Paul Newcome āœ­āœ­āœ­āœ­āœ­āœ­
    Answer āœ“

    One a separate sheet, you would first use a text/number column (called "Number" in this example). This column will be manually populated with the numbers 1 through however many you anticipate needing plus a buffer just in case. So if you think you will never need more than 100, I would suggest going down to 125 just in case. It won't hurt anything to have it there and will allow for some unexpected scaling if needed.


    In the next text/number column you would use this column formula:

    =IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Dropdown Column}, {Source Sheet Dropdown Column}, @cell <> "")), Number@row), "")

    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

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar āœ­āœ­āœ­āœ­āœ­āœ­

    Hello!

    This is possible with the API . If you don't have that capacity we can implement this for you

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/šŸ’”Insightful, ā¬†ļø Vote Up, ā¤ļøAwesome!

  • Paul Newcome
    Paul Newcome āœ­āœ­āœ­āœ­āœ­āœ­

    You can't do this natively using the dropdown options directly, but you can leverage some formulas to populate your metrics sheet with the options that have been selected. If that is an option, let me know, and I would be happy to walk you through it here.


    If you have access to Data Shuttle, you can set things up so that you manage the list of names on the metrics sheet and then Data Shuttle can grab from the metrics list and update the dropdown for you. I would be happy to walk you through that one as well if needed.

    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

  • Muhammad
    Muhammad āœ­āœ­āœ­

    Hi Paul! Yes, this would be super helpful! What kind of Formulas would I have to implement to have this list populated?

  • Paul Newcome
    Paul Newcome āœ­āœ­āœ­āœ­āœ­āœ­
    Answer āœ“

    One a separate sheet, you would first use a text/number column (called "Number" in this example). This column will be manually populated with the numbers 1 through however many you anticipate needing plus a buffer just in case. So if you think you will never need more than 100, I would suggest going down to 125 just in case. It won't hurt anything to have it there and will allow for some unexpected scaling if needed.


    In the next text/number column you would use this column formula:

    =IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Dropdown Column}, {Source Sheet Dropdown Column}, @cell <> "")), Number@row), "")

    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

  • Paul Newcome
    Paul Newcome āœ­āœ­āœ­āœ­āœ­āœ­

    Happy to help. šŸ‘ļø

    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

  • David Jasven
    David Jasven āœ­āœ­āœ­āœ­

    Hi Muhammad,

    I understand the need to automate the population of an entire column based on a dropdown menu from another sheet. I faced a similar situation and found it challenging to update manually each time someone joined or left. To streamline this, I developed a solution that allows columns to auto-populate based on values from another sheet.

    You can see how it works in this demo video: . If it looks like a fit for your needs, feel free to sign up to use this feature.

    I'm here to help if you have any questions or need further assistance.

    āœ… 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 PARTNER

    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!