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]

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

View attachment 117751

Hi Peter

Using the formula I included seemed to have removed the blank cells. I assumed I had to do all that to then get the list alphabetical? But I will try your method. I probably did take a very long winded way around as I was learning as I went. And I have learnt I don't need the formula in C1 but I assume I would need the Name Manager which sorts the list alphabetically? But I will try your suggestion.

I appreciate all your help

 

[Peter_SSs]

Using the formula I included seemed to have removed the blank cells.

If the cells are truly empty then your formula will remove them (via the TOCOL function). However, if any cells contain "" as the result of another formula then the final list would have one apparently blank cell in it.

I assume I would need the Name Manager which sorts the list alphabetically?

It is not Name Manager that sorts the list it is the long formula in A1

 

[FatalLordes]

If the cells are truly empty then your formula will remove them (via the TOCOL function). However, if any cells contain "" as the result of another formula then the final list would have one apparently blank cell in it.


It is not Name Manager that sorts the list it is the long formula in A1

LOL, I just did as you said and yep, it works.... so I learnt a VERY long winded way of doing something very easy LOL! Thank you so much again!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.