How do I eliminate the blank cell value in data validation list without creating a new column to sort out the empty cell?
Formula for my define name
=MASTER!$C$2:INDEX(MASTER!$C$2:$C$100000,SUMPRODUCT(--(MASTER!$C$2:$C$100000<>"")))
Asked
Active
Viewed 900 times
0
Giorgos Myrianthous
- 36,235
- 20
- 134
- 156
Lim
- 75
- 1
- 7
-
What do you mean by eliminating the blank cell value ? – M.Douda Aug 20 '18 at 08:44
-
like do not show it. Currently for empty value, the drop down list will have a blank space which i do not want it to show – Lim Aug 20 '18 at 08:59
-
Are your Blanks in the middle of the list, or all at the end? – Chronocidal Aug 20 '18 at 11:23
-
in the middle of the list – Lim Aug 21 '18 at 01:14
1 Answers
0
In google sheets i would use UNIQUE which returns a list of unique values. This would remove empty values. To do it in excel is a little trickier but you can still do it:
Filter all unique items like Google Docs
Edit: As @m-douda mentioned this would also eliminate any duplicate records that are not empty.
Marco Yammine
- 323
- 1
- 10
-
1
-
You are right. I will update the answer. However I assumed that this would actually be the expected behavior as when generating a drop down you definitely don't want duplicates as you don't have keys to link it to. – Marco Yammine Aug 20 '18 at 09:24