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

Find more posts tagged with

Sheets

Formulas

Reports

Automations

Sheet Summary

Accepted answers

Paul Newcome

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), "")

All comments

SoS | Dan Palenchar

Hello!

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

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.

Muhammad

https://community.smartsheet.com/discussion/comment/419879#Comment_419879

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

Paul Newcome

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), "")

Muhammad

https://community.smartsheet.com/discussion/comment/420184#Comment_420184

That worked Beautifully, thank you so much!

Paul Newcome

Happy to help. 👍️

David Jasven

https://community.smartsheet.com/discussion/117503/populate-column-from-another-sheets-dropdown-menu

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.

https://youtu.be/8AYfwDlMoDg?si=jqPfwd22YIAdDQKh

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.