Just Joined the site (Hi Everyone :D )
I am trying to create a drop-down menu on worksheet 1, where the data is on worksheet 2.
The Data is based in column 'A' ONLY, BUT has blank cells which the drop down menu picks up.
Example data
1 - [Text here]
2 - [blank cell]
3 - [Text here]
4 - [Blank Cell]
5 - [Blank Cell]
6 - [Text here]
Drop down menu currently displaying the same as above.
What i want it to look like is;
1 - [Text here]
3 - [Text here]
6 - [Text here]
So it will ignore all the blank cells and just process to the next cell populated with text.
i have tried using the following formulae;
=IFERROR(INDEX($A$2:$A$40,AGGREGATE(15,3,ROW($A$2:$A$40)-ROW($A$1)/($A$2:$A$40<>""),ROWS($A$2:A2))),"")
But when i enter it into data validation as a list, i get an error "The list source must be a delimited list, or a reference to a single row or column.
Any help would be appreciated
EDIT: Tried to insert my worksheet, but can't exactly find out how ( paperclip icon does not allow me to do anything.
Bookmarks