Named formula not seen from validation list source?

**reggery** · 11-12-2009, 03:06 PM

(Mac Excel 2008) I want to populate a data validation list depending on the value in cell G11. G11 can have 1 of (for this example) 6 values: "ABC", "DEF", "GHI", etc. (In practice there are 35 values.) The six cell ranges containing the list contents in each case are at various locations within column CB.

I'm using nested formulae, each of which is a nested IF statement. The top level IF statement is the Source (Data -> Validation... -> Source text field) which first references the named formulae and then, on failure, checks its own false clause (ABC and DEF here). If nothing matches then cell CC1 is used.

For example:

This is the source statement for the drop-down cell:
=IF(NESTED_IF_0,NESTED_IF_0,IF(G11="ABC",CB1:CB10,IF(G11="DEF",CB11:CB20,CC1:CC1)))

These are the named formulae:
NESTED_IF_0:
=IF(NESTED_IF_1,NESTED_IF_1,IF(G11="GHI",CB21:CB30,IF(G11="JKL",CB31:CB40,FALSE)))

NESTED_IF_1:
=IF(G11="MNO",CB41:CB:50,IF(G11="PQR",CB51:CB60,FALSE))

(Actually I have 6 formulae to an IF depth of 6 for 35 different values in G11.)

The problem is: NESTED_IF_0 always returns false, as though it can't be seen, regardless of what's in G11. It's definitely defined and the syntax is correct. If I plug any one of these formulae directly into a cell it seems to work as it should, but within the data validation list source they don't seem to be seen.

Thanks for reading this far, and thanks in advance for any insight you can provide as to why it might not be seeing the named formula.

- Dan

**teylyn** · 11-12-2009, 05:16 PM

Hi,

my previous experience leads me to believe that the Source box of data validation can only take so much. Evaluating a formula whose result is another formula usually kicks it over.

Maybe you can put your IF statement in a cell and in the data validation box use INDIRECT to refer to the cell. You'll have to make sure the output of the cell is text, though, so maybe a formula should be in A1

=IF(NESTED_IF_0,NESTED_IF_0,IF(G11="ABC","CB1:CB10",IF(G11="DEF","CB11:CB20","CC1:CC1")))

then in the data validation source use =INDIRECT(A1)

I have tested this on a small data sample and it worked fine.

hth

**Cheeky Charlie** · 11-12-2009, 07:06 PM

Nesting ifs this far almost certainly implies poor function structure (no offence). Of course, I can't test this without seeing more of your workbook, I would recommend this:http://www.contextures.com/xlDataVal13.html for data validation lists (and, more to the point, dependent validation lists).

CC

**reggery** · 11-12-2009, 08:23 PM

Teylyn, CC -

Thanks a lot for both informative responses. Since I've posted a couple hours ago I've come to the same conclusions you have. The 35 options were foisted upon me - that was a given. Googling around, it seemed that the nested IFs were the only approach that made sense; I wrote a Perl script to generate all those sick IF statements instead of typing so I know the syntax was okay.

A co-worker pointed me to the contextures solution - I wasn't aware of the INDIRECT function (I'm kind of new to this) so that's what I'll do. Still, I'd like to know why the first clumsy approach didn't work. Maybe it got kicked over as you say and that's just the way it is :-)

Thanks again for your trouble...

- Dan