| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 117220 | 2011-04-08 05:53:00 | Excel nested if question | ecurb (3669) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 1192929 | 2011-04-08 05:53:00 | I'm trying to do a nested IF of approx 30 IF's. I know Excell only nests 7 times but there must be another way to get the answer I want. Formula is IF($A$4=8,'8'!B6,IF($A$4=9,'9'!B6,IF($A$4=10,'10'! B6 etc etc with 30 different tables I am looking up from. Thanks in advance Is there a better forum to ask this question? |
ecurb (3669) | ||
| 1192930 | 2011-04-08 08:04:00 | www.mrexcel.com | pctek (84) | ||
| 1192931 | 2011-04-08 11:06:00 | Not sure if this would work - but what I would be investigating at is a macro using SELECT CASE? | johnd (85) | ||
| 1192932 | 2011-04-08 11:07:00 | Have you tried something like INDIRECT($A$4 & "!B6")? So, for example, if A4 contained 'Sheet2', then you'd get the contents of Sheet2!B6. I'm guessing that you have a bunch of sheet tabs named '1', '2' ... '30' & want to look up a value from a particular sheet depending on A4's value. Bonus tip: If you want a formula you can copy & keep the relative cell references (say, to fill a column), you could start with INDIRECT($A$4 & "!" & CELL("address",B6)). Then if you copy the formula, B6 will change to the correct relative position & the result of the string will change with it. |
MushHead (10626) | ||
| 1192933 | 2011-04-08 11:51:00 | spreadsheetpage.com "Excel 2007 and later allows up to 64 nesting levels" |
MartynC (5610) | ||
| 1192934 | 2011-04-09 01:45:00 | Thanks pctek. Works like a charm. | ecurb (3669) | ||
| 1 | |||||