Populate Column From Another Sheet's Dropdown Menu
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
-
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), "")
Answers
-
Hello!
This is possible with the API . If you don't have that capacity we can implement this for you
If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.
👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner
- 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
- ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube // list view in Smartsheet
PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!
I make YouTube videos answering community questions: see if yours is on the list here!
-
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.
-
Hi Paul! Yes, this would be super helpful! What kind of Formulas would I have to implement to have this list populated?
-
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), "")
-
That worked Beautifully, thank you so much!
-
Happy to help. 👍️
-
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
Categories
Check out the Formula Handbook template!
