Create list from multiple cells on multiple sheets?

[FatalLordes]

Hi all

So I have have multiple cells across multiple sheets that I want to sort into one list which I can then use to create a drop down list. I want it to exclude any empty cells and I only want unique values.

My cells are all the same on both sheets, specifically H10:H22 and H26:H38 and my sheets are "2004" and "2005".

How would I go about doing this? Is it possible? I will gradually be adding more sheets but the cell numbers will always be the same on every sheet.

Would really appreciate some help and guidance.

 

[FatalLordes]

Ok, so I'm thinking I'm getting on the right path:

=UNIQUE(VSTACK('2024-25'!H186:H198,'2024-25'!H170:H182))

But it is showing me one #VALUE cell for cells that still have a formula in them as they haven't triggered yet. Trying to work out how to exclude these. I'll keep playing in case I work it out however....

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Your ranges and sheet names in those two posts are completely different, but try this

=UNIQUE(TOCOL(VSTACK('2024-25'!H186:H198,'2024-25'!H170:H182),3))

 

[FatalLordes]

Ok so I worked out if I do the "" at the end of the formula, it gets rid of the zeros but I can 't seem to get rid of the #VALUE!....

... but I can live with #VALUE! but it lead me to my next issue....

I want to sort the list in alphabetical order, because ultimately I want this list to form a drop down list and being in alphabetical order will be so much easier. I used the below formula in a new column (C) to do this and it worked fine:

=IF(ISBLANK(SORT(A:A,1,1)),"",SORT(A:A,1,1))

But now when I use Data Validation to create a drop down using the below formula, I get the data (yay) but heaps and heaps of blank lines (boo). The list will obviously grow so I can't set a defined end but I don't want all the blanks.

='Data Validation'!$C:$C

Or am I making this a whole pile harder than it needs to be?

 

[FatalLordes]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Your ranges and sheet names in those two posts are completely different, but try this

=UNIQUE(TOCOL(VSTACK('2024-25'!H186:H198,'2024-25'!H170:H182),3))

Oh good thought, I will do that. For the record I'm using Win 11 and Excel 365

 

[FatalLordes]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Your ranges and sheet names in those two posts are completely different, but try this

=UNIQUE(TOCOL(VSTACK('2024-25'!H186:H198,'2024-25'!H170:H182),3))

That code worked brilliantly. Thank you!

I'm now working on how I can then take the converted alphabetised column to turn that into a drop down list that doesn't show all the blanks.

 

[Peter_SSs]

Thanks for the profile update.

Some sample data with XL2BB would be good so we had something realistic to test with but try

=LET(a,UNIQUE(TOCOL(VSTACK('2024-25'!H186:H198,'2024-25'!H170:H182),3)),FILTER(a,a<>""))

 

[FatalLordes]

I worked it out! Wooo!

So I first used the following code in A1 on a "Data Validation" sheet (with thanks to @Peter_SSs ):

=SORT(UNIQUE(TOCOL(VSTACK('2024-25'!H186:H198, '2024-25'!H170:H182, '2024-25'!H154:H166, '2024-25'!H138:H150, '2024-25'!H122:H134, '2024-25'!H106:H118, '2024-25'!H90:H102, '2024-25'!H74:H86, '2024-25'!H58:H70, '2024-25'!H42:H54, '2024-25'!H26:H38, '2024-25'!H10:H22, '2025-26'!H186:H198, '2025-26'!H170:H182, '2025-26'!H154:H166, '2025-26'!H138:H150, '2025-26'!H122:H134, '2025-26'!H106:H118, '2025-26'!H90:H102, '2025-26'!H74:H86, '2025-26'!H58:H70, '2025-26'!H42:H54, '2025-26'!H26:H38, '2025-26'!H10:H22<>""),3)))

I then used the following code in C1 on the same sheet:

=$A$1:INDEX($A$1:$A$1000,SUMPRODUCT(--($A$1:$A$1000<>"")))

And I saved that formula to the Name Manager (Ctrl F3) as ReferenceList

Then in my drop down on another sheet I put the formula in as =ReferenceList. And it works! Alphabetised list with no blanks at the end!

I'm not sure I need to keep the formula in C1 given I saved it in the Name Manager (which I never even knew existed)....

 

[FatalLordes]

Sorry, I made a mistake with one post above. Please change the relevant code to:

=SORT(UNIQUE(TOCOL(VSTACK('2024-25'!H186:H198, '2024-25'!H170:H182, '2024-25'!H154:H166, '2024-25'!H138:H150, '2024-25'!H122:H134, '2024-25'!H106:H118, '2024-25'!H90:H102, '2024-25'!H74:H86, '2024-25'!H58:H70, '2024-25'!H42:H54, '2024-25'!H26:H38, '2024-25'!H10:H22, '2025-26'!H186:H198, '2025-26'!H170:H182, '2025-26'!H154:H166, '2025-26'!H138:H150, '2025-26'!H122:H134, '2025-26'!H106:H118, '2025-26'!H90:H102, '2025-26'!H74:H86, '2025-26'!H58:H70, '2025-26'!H42:H54, '2025-26'!H26:H38, '2025-26'!H10:H22),3)))

 

[Peter_SSs]

1. What happened to the issue of 'blank' cells as mentioned in post 6?
   If there are any formula cells returning "", doesn't that formula produce a 'vacant' cell A1 on the 'Data Validation' sheet?
   
   
2. You have gone to a lot of unnecessary work to use the results in a data validation drop-down.
   - You do not need the formula in C1
   - You do not need anything in the Name Manager
   - In any cells where you want the list as drop-down values use data validation -> Allow: List -> in the Source box put this formula
   ='Data Validation'!$A$1#
   (ensuring that the blue text is the name of the worksheet where your long formula resides in cell A1

BTW, when posting a formula in the forum, use the icon indicated below, not the 'Inline code' icon