I am trying to do the same thing and the second list will only work for 4 IF
functions, and I have 6. So, how do I get around that limit?

"trexcel" wrote:

I found this post very helpful and it answered my questions without posting a
new string. However, I have my first drop down list looking at 20 different
names, and my second dropdown list looking at 20 different lists, contingent
on which name I choose from the first list. There seems to be a limit on how
many levels you can do with the IF function, because I am getting errors when
I try to do this many.

To isolate the problem try this formula in a blank spread sheet, in say cell
B1:

=IF(A1=1,M1:M10,IF(A1=2,N1:N10,IF(A1=3,O1:O10,IF(A 1=4,P1:P10,IF(A1=5,Q1:Q10,IF(A1=6,R1:R10,IF(A1=7,S 1:S10,IF(A1=8,T1:T10,0))))))))

That works but if you add another two "levels":

=IF(A1=1,M1:M10,IF(A1=2,N1:N10,IF(A1=3,O1:O10,IF(A 1=4,P1:P10,IF(A1=5,Q1:Q10,IF(A1=6,R1:R10,IF(A1=7,S 1:S10,IF(A1=8,T1:T10,IF(A1=9,U1:U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Would the same issues face us if we went to a combobox? (I am
not very familiar with combo boxes) Do you have any remedy for this at all?



"NoBloatwarePlease" wrote:

Comrade Burlingham,

This is actually pretty simple, it just took me about a half hour to figure
out what the syntax is for the IF formula you put in your secondary (or
tertiary, or quaternary, whatever) dropdown list validation cell.

Example: I have a basic list of general property types: Retail, office and
industrial. In cell B1, I want the user to select one of the 3 types. In
B2, though, I want her to select the SUBtype; i.e., if she selects Retail in
B1, I want her to see only the retail subtypes in B2. If she selects Office
or Industrial in B1, I want her to see only the Office or Industrial subtype
list when she clicks the B2 dropdown. Let's say I've created the four
(General, Retail, Office and Industrial) dropdowns in another sheet in the
same workbook and have given each range the names George, Ronald, Oscar and
Izzy, though giving them the same names as the categories is okay, too).
Then I go back to cell B1 in my main sheet and select Data | Validation
(Excel 2007), choose List and write in the formula, =George. When done, if I
select cell B1, I'll have a dropdown that lists Retail, Office and
Industrial. Then I go to cell B2, select Data | Validation, choose List,
and--here's the secret--enter an IF formula as follows:

=IF(B1="Retail",Ronald,IF(B1="Office",Oscar,IF(B1= "Industrial",Izzy,"Wrongamundo, Buckwheat!")))

There are 3 IF statements, so you have to have 3 closing parens on the end,
of course. But the trick is to identify the text in B1 in quotes (because
text is what Excel will see there), and the named range WITHOUT quotes,
because Excel sees it as a range address, not as text. The formula really
won't return "Wrongamundo, Buckwheat!" if the user fails to select one of the
three types, because those are the only ones I allowed. But I could have
allowed a user-created entry.

Pretty slick, eh? The cool part is you can create as many dropdown levels
as you have levels of patience to enter long, nested "IF" formulas...
__________________________
"Sburlingham" wrote:

I have a problem, i created a drop down list of jobs that could have been
preformed i need to create another list for each of those jobs to be more
specific with what exactly was preformed i had this great idea that maybe if
i picked for example warranty work that another list would drop down and let
me pick manufacturing or vendor then another list would drop down and i could
pick what kinda manufact. or vendor warranty it was. this may not be possible
though it would be a great thing to implement in the future i need a way that
is user friendly to detail what work was done. any ideas?

I do not think you need to use all the IF statements. You can use the INDIRECT function in your list validation instead which will save you entering all the IF levels. Just give your sub-lists the same names available in the top-level list (e.g. "General", "Retail", "Office" and "Industrial" instead of "George", "Ronald", "Oscar" and "Izzy".)

Example
In the following example the user will have two options available to choose in Cell E1. The options available to select in Cell F1 will depend on what they select in E1.

Cells A1:A2 contain a named range called "WorkType" containing the values "Retail", "Office" (These will be the top level options available to the user)
Cells B1:B2 contain a named range called "Retail" containing the values "Retail option 1" and "Retail option 2". #(These are the second level options that will be available if the user selects "Retail")
Cells C1:C2 contain a named range called "Office" containing the values "Office Option 1" and "Office Option 2". #(These are the second level options that will be available if the user selects "Office")

Cell E1 will be where the user chooses their top level option
Cell F1 will be where the user selects their second level option

For Cell E1 choose DataValidation, select Lists and in the Source box enter =WorkType (including the "=" symbol)

For Cell F1 choose DataValidation, select Lists and in the Source box enter =INDIRECT($E1) (including the "=" symbol again)

If the user selects "Retail" in E1 then the drop down list in F1 will contain "Retail Option 1" and "Retail Option 2", but if the user selects "Office" in E1 then the F1 list will contain "Office Option 1" and "Office Option 2"






----------------
Now playing: Neil Diamond - Captain Of A Shipwreck
via FoxyTunes