Blanks Data Validation List

[FARAZ QURESHI]

Cells A1:A10 show a result from a formula. A5 is containing no formula and A8
reflects "" as a formula result.

Now when I select the range A1:A10 in Data>Validation>List source being
A1:A10 I want only the results which are neither Blank nor "" but the drop
down list still reflects blank spaces of A5 & A8, although the "Ignore
Blanks" have been checked.

All expertise and guidance shall be obliged.

Thanx in advance!

 

[Gary''s Student]

This is a very clumsy solution. Ignore it if a better one is posted:

Say the data is in column B rather than column A. From B1 thru B10:

anteater
bird
cat
dog

elephant
flea

gyron
horse

As in your case both B5 and B8 are empty. In A1 enter 1 and in A2 enter:
=IF(B2="","",MAX($A$1:A1)+1) and copy down. We now have:

1 anteater
2 bird
3 cat
4 dog

5 elephant
6 flea

7 gyron
8 horse

Now we can use VLOOKUP() on column A. In C1 enter:
=IF(ISERROR(VLOOKUP(ROW(),A:B,2,FALSE)),"",VLOOKUP(ROW(),A:B,2,FALSE))
and copy down:

1 anteater anteater
2 bird bird
3 cat cat
4 dog dog
elephant
5 elephant flea
6 flea gyron
horse
7 gyron
8 horse

Column C is like column B except all empties have been removed.

Use column C as the validation list rather than column B

 

[Max]

One way to get there ..

Assume the source for the DV is in Sheet1's A1:A10
In C1: =IF(A1="","",ROW())
In D1: =INDEX(A:A,SMALL(C:C,ROW()))
Select C11, copy down to D10

Then you could use this expression as the dynamic range for your DV:
=OFFSET(Sheet1!$D$1,,,SUMPRODUCT(--NOT(ISERROR(Sheet1!$D$1:$D$10))))
where the height param is given by the sumproduct expression on the derived
col D as the proxy source. The DV droplist will then return the results that
you seek (ie expand or contract accordingly).

If the above works (it should), do take a moment to press the "Yes" button
below to the question: "Was this post helpful to you?" from where you're
reading this. This'll ensure a longer shelf life to this thread for the
general benefit of other readers.

 

[Max]

GS, I don't think my suggestion is any neater

Just some thoughts on your suggestion
Instead of:

.. In A1 enter 1

Suggest in A1: =IF(B1="","",1)
(as the 1st source cell B1 may be blank)

Then to

.. Use column C as the validation list rather than column B

the OP could use this expression as the dynamic range for his DV:
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT(--(Sheet1!$C$1:$C$10<>"")))

---

 

[Gary''s Student]

Good suggestion
--
Gary''s Student - gsnu200773

GS, I don't think my suggestion is any neater

Just some thoughts on your suggestion
Instead of:
Suggest in A1: =IF(B1="","",1)
(as the 1st source cell B1 may be blank)

Then to
the OP could use this expression as the dynamic range for his DV:
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT(--(Sheet1!$C$1:$C$10<>"")))

---