I have attached an example of what I am trying to do.
On the "Selectable" sheet I wish to have the data validation in the orange section without blanks. At the moment, because the data validation drop-down box is from the "data" row and not every cell has a label, then there are blanks appearing in the data validation list.
I would like these blanks to be removed.
Any ideas?
Cheers,
Graeme
One way would be to assemble a blank free list and use this array formula to generate the Data validation source:
=IFERROR(INDEX($A$1:$T$1, SMALL(IF(ISBLANK($A$1:$T$1), "", COLUMN($A$1:$T$1)-MIN(COLUMN($A$1:$T$1))+1), ROWS($1:1))),"")
I used this formula in Data validation:
=OFFSET(data!$AA$2,,,SUMPRODUCT(--(LEN(data!$AA$2:$AA$100)>0)))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
It would be best just to make a list in a separate range with no blanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks