(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.
This is the source statement for the
the named formulae:
(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.