# Blanks Data Validation List

F

#### 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.

G

#### 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

M

#### 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
reading this. This'll ensure a longer shelf life to this thread for the

M

#### Max

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

Just some thoughts on your suggestion
.. 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<>"")))

---

G

#### Gary''s Student

Good suggestion
--
Gary''s Student - gsnu200773

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

Just some thoughts on your suggestion
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<>"")))

---