Dynamic Dropdown - Data Shutttle

Sabina Compassi

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?

Paul Newcome

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.

Sabina Compassi

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