Dynamic Dropdown - Data Shutttle
I want to create a dynamic dropdown in a sheet.
The data for my dropdown reside in 6 different sheets, so I thought it would be smart to consolidate using the report function. Now the data are in a report.
How can I create a dynamic dropdown with the data source being in the report rather than in a sheet? I cannot select the report for the data shuttle option.
If that is not possible, how can I create a drop down list out of data in 6 different sheet?
Answers
-
I would set up 8 columns. The first is a text/number called "Number". This one will be manually populated with numbers starting with 1 on the first row and going down to accommodate however many you think you will need total.
Next 6 columns are for each of your 6 sheets. They are going to have a column formula along the lines of
=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Column To Pull}, {Source Sheet Column To Pull}, @cell <> "")), Number@row), "")
You will need to create a new cross sheet reference for each sheet.
The final column is where you final list will live. That is going to have a column formula along the lines of
=IFERROR(INDEX(DISTINCT(COLLECT([1st Sheet Column]:[Last Sheet Column], [1st Sheet Column]:[Last Sheet Column], @cell <> "")), Number@row), "")
Then you can set up your Data Shuttle to pull from this last column.
-
Let me try and see if this works.
Looks like an elegant solution.
(I already have found a solution that works - several data shuttles in sequence - looks like back to stoneage…. ) but yours looks super good and elegant..Kind regards
Sabina
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives