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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!