| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLP! Nesting Problem??
Hi,
Am wondering why the first formula below works fine, but when I modify it with an INDIRECT in the last line, it keep telling me I have an error. THIS WORKS: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,USA!$B:$D,3,FALSE),"ER ROR"))))))) THIS DOESN'T WORK: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,INDIRECT($H$1&"!$B:$D" ),3,FALSE),"ERROR"))))))) -- Thanks! Dee |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HLP! Nesting Problem??
Hi
Why not create named ranges for Canada, France Germany etc. then just use =VLOOKUP($AA16,INDIRECT($Y16),5,FALSE), -- Regards Roger Govier "dee" wrote in message ... Hi, Am wondering why the first formula below works fine, but when I modify it with an INDIRECT in the last line, it keep telling me I have an error. THIS WORKS: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,USA!$B:$D,3,FALSE),"ER ROR"))))))) THIS DOESN'T WORK: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,INDIRECT($H$1&"!$B:$D" ),3,FALSE),"ERROR"))))))) -- Thanks! Dee |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HLP! Nesting Problem??
I had thought of that, but they are in separate worksheets and the column
number referenced may be different, so I don't think it will work? -- Thanks! Dee "Roger Govier" wrote: Hi Why not create named ranges for Canada, France Germany etc. then just use =VLOOKUP($AA16,INDIRECT($Y16),5,FALSE), -- Regards Roger Govier "dee" wrote in message ... Hi, Am wondering why the first formula below works fine, but when I modify it with an INDIRECT in the last line, it keep telling me I have an error. THIS WORKS: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,USA!$B:$D,3,FALSE),"ER ROR"))))))) THIS DOESN'T WORK: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,INDIRECT($H$1&"!$B:$D" ),3,FALSE),"ERROR"))))))) -- Thanks! Dee |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HLP! Nesting Problem??
I believe your problem is you exceeded excels limit of 7 nested function
levels. I would try using Roger's named range approach. You could set up a table to identify the column number to return. Lets say in V19:W25 Canada 5 France 5 Germany 5 Italy 5 Swiss 5 UK 5 USA 3 and the formula s/b =IF(ISNUMBER(MATCH($AA16,$V$19:$V$25,0)),VLOOKUP($ AA16,INDIRECT($Y16),VLOOKUP($AA16,$V$19:$W$25,2,0) ,0),"ERROR") "dee" wrote: I had thought of that, but they are in separate worksheets and the column number referenced may be different, so I don't think it will work? -- Thanks! Dee "Roger Govier" wrote: Hi Why not create named ranges for Canada, France Germany etc. then just use =VLOOKUP($AA16,INDIRECT($Y16),5,FALSE), -- Regards Roger Govier "dee" wrote in message ... Hi, Am wondering why the first formula below works fine, but when I modify it with an INDIRECT in the last line, it keep telling me I have an error. THIS WORKS: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,USA!$B:$D,3,FALSE),"ER ROR"))))))) THIS DOESN'T WORK: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,INDIRECT($H$1&"!$B:$D" ),3,FALSE),"ERROR"))))))) -- Thanks! Dee |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HLP! Nesting Problem??
Thanks to both of you, I have a couple of options.
Thanks! -- Thanks! Dee "JMB" wrote: I believe your problem is you exceeded excels limit of 7 nested function levels. I would try using Roger's named range approach. You could set up a table to identify the column number to return. Lets say in V19:W25 Canada 5 France 5 Germany 5 Italy 5 Swiss 5 UK 5 USA 3 and the formula s/b =IF(ISNUMBER(MATCH($AA16,$V$19:$V$25,0)),VLOOKUP($ AA16,INDIRECT($Y16),VLOOKUP($AA16,$V$19:$W$25,2,0) ,0),"ERROR") "dee" wrote: I had thought of that, but they are in separate worksheets and the column number referenced may be different, so I don't think it will work? -- Thanks! Dee "Roger Govier" wrote: Hi Why not create named ranges for Canada, France Germany etc. then just use =VLOOKUP($AA16,INDIRECT($Y16),5,FALSE), -- Regards Roger Govier "dee" wrote in message ... Hi, Am wondering why the first formula below works fine, but when I modify it with an INDIRECT in the last line, it keep telling me I have an error. THIS WORKS: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,USA!$B:$D,3,FALSE),"ER ROR"))))))) THIS DOESN'T WORK: =IF($Y16="Canada",VLOOKUP($AA16,INDIRECT($Y16&"!$B :$F"),5,FALSE), IF($Y16="France",VLOOKUP($AA16,INDIRECT($A$1&"!$I$ 4:$M$35"),5,FALSE), IF($Y16="Germany",VLOOKUP($AA16,INDIRECT($A$1&"!$O $4:$S$35"),5,FALSE), IF($Y16="Italy",VLOOKUP($AA16,INDIRECT($A$1&"!$U$4 :$Y$35"),5,FALSE), IF($Y16="Swiss",VLOOKUP($AA16,INDIRECT($A$1&"!$AA$ 4:$AE$35"),5,FALSE), IF($Y16="UK",VLOOKUP($AA16,INDIRECT($A$1&"!$AG$4:$ AK$35"),5,FALSE), IF($Y16="USA",VLOOKUP($AA16,INDIRECT($H$1&"!$B:$D" ),3,FALSE),"ERROR"))))))) -- Thanks! Dee |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Function Nesting Problem | Excel Worksheet Functions | |||
| Another Nesting IF Statement Problem | Excel Worksheet Functions | |||
| Nesting Problem | Excel Worksheet Functions | |||
| IF - Nesting... almost got it - need a bit of help | Excel Worksheet Functions | |||
| nesting sum if and | Excel Worksheet Functions | |||
Linear Mode
