Ignoring Black Cells in Validation Drop-Down List

**Moxcel** · 10-14-2010, 04:17 PM

I'm wondering if anyone has a formula for ignoring blank cells in a data validation drop-down list that is referencing cells in columns.

This forum topic shows how to do it from data in rows, but my data is all in a column (and I don't want to have to transpose it every time I add a new cell):

http://www.excelforum.com/excel-gene...ml#post2400161

Post #3 has a great example worksheet. I need an equivalent formula that uses column data instead of rows.

As far as I'm aware, I can't use VBA code because I'm using Office 2008 for Mac.

Thanks.

**JBeaucaire** · 10-14-2010, 04:58 PM

Like so....:

**Moxcel** · 10-14-2010, 06:12 PM

Thanks for the help. That's exactly what I want. The only problem I'm having is when I copy the formula into my workbook to make changes, all the empty cells now read #NUM!. I'm currently checking out my workbook to locate the problem. I have a feeling it's something to do with one of my formulas.

**JBeaucaire** · 10-14-2010, 08:53 PM

I'm going to guess you edited the formula from using a set range to trying to use an entire column. Don't do that. These are array formulas, they do 100s of calculations over and over, just set the range to what you need a *little* more.

**Moxcel** · 10-15-2010, 02:12 PM

Originally Posted by JBeaucaire

I'm going to guess you edited the formula from using a set range to trying to use an entire column. Don't do that. These are array formulas, they do 100s of calculations over and over, just set the range to what you need a *little* more.

Thanks for the tip, though this isn't the problem.
I've realized the problem is that my "blank" cells aren't technically blank which is why it's returning #NUM!. I have an IF formula in those cells which leaves them empty (ie. =IF(A1="","",A1), if the cell it's referencing is blank. But because it contains that formula, I guess it's not considered blank. At least I think this is the problem.