Dropdown list with empty cells by formula
Solved
BaptisteGuilbert
Posted messages
18
Status
Member
-
BaptisteGuilbert Posted messages 18 Status Member -
BaptisteGuilbert Posted messages 18 Status Member -
Hello,
I'm reaching out to ask for help regarding a dropdown list I would like to create in Excel.
The list I want to build in a dropdown format uses a list where most of the cells are empty (these are "spare" spots in case the people using the file I created add data), and therefore when I create the dropdown list, it has countless blank (white) rows that make the list impractical.
I have tried many methods proposed on the internet (OFFSET, INDEX), but nothing seems to work (or maybe I didn't approach it correctly), I think it comes from the fact that the cells are not "empty" per se, but they contain formulas that return a blank result. This makes me think that the "Ignore blank" function when validating data in Excel does not consider my empty cells due to the formula.
Below is my file with the list from which I would like to remove the blank rows in column H.
https://www.cjoint.com/c/IGzo6xqy1fY
Thank you in advance for your help,
Baptiste
I'm reaching out to ask for help regarding a dropdown list I would like to create in Excel.
The list I want to build in a dropdown format uses a list where most of the cells are empty (these are "spare" spots in case the people using the file I created add data), and therefore when I create the dropdown list, it has countless blank (white) rows that make the list impractical.
I have tried many methods proposed on the internet (OFFSET, INDEX), but nothing seems to work (or maybe I didn't approach it correctly), I think it comes from the fact that the cells are not "empty" per se, but they contain formulas that return a blank result. This makes me think that the "Ignore blank" function when validating data in Excel does not consider my empty cells due to the formula.
Below is my file with the list from which I would like to remove the blank rows in column H.
https://www.cjoint.com/c/IGzo6xqy1fY
Thank you in advance for your help,
Baptiste
1 answer
-
Hello Baptiste
All you need to do in the Source of the Dropdown Validation is put this formula:
=OFFSET($E$2;;;COUNTIF($E:$E;">=a")-1)
The list will expand as new items are added.
Normally, OFFSET is used with COUNTA, which counts the number of non-empty cells, but those with formulas are counted, which is why in this case it’s better to test for the presence of characters using COUNTIF(..;">=a").
Best regards
Via
