Too many arguments

[Jambruins]

I have 31 sheets with info on them. I am trying to get a formula i
sheet 31 to do as follows. Column A has a word in it that correspond
to on of the other 30 sheets. I want a cell in column to pick a valu
from the sheet that column A corresponds to. It is working fine wit
an if function until I enter in my 8th argument. It says I hav
entered too many arguments. Is there a way around this?

For example sheet 1 is Ana, sheet 2 is Atl. I want cell B2 in sheet 3
to look at cell A2 and if it says Ana pick cell H2 from sheet 1 or i
it says Atl to pick cell H2 from sheet 2.

Can I do this with some other equation or can I only use an if statmen
with 7 equations? Thank

 

[Stephen Bye]

If the sheets are actually named "Ana", "Atl", etc. then in sheet 31 cell B2
just put the formula:
=INDIRECT(A2&"!H2")

If the sheets are names "Sheet1", "Sheet2", etc. then use a lookup table to
convert the word to the sheet name first.

 

[Jambruins]

if you think you can help me but don't understand my question please le
me know and I will try and reword it. Thanks

 

[Bob Umlas]

The IF-statement is limited to 7 nestings, but you can have nearly an
unlimited number of them if you don't nest them, which isn't always
possible, but applicable in some instances, and this is one of them. Use
something like this:
=IF(A2="Ana",Sheet1!H2,"")&IF(A2="Atl",Sheet2!H2,"")
Notice there's no nesting, so this procedure can be carried out with many
"&"'s.
However, if A2 contains the actual name of the sheet, you can do even better
by using:
=INDIRECT(A2&"!H2") and you're done!


I teach a live online Excel Master Class. It starts March 2, 2004 and occurs
6-9PM Tuesday evenings ending 6/1/04.

For details, please follow this link:

http://www.iil.com/str_link_all_results.asp?select_cartid=395


Bob Umlas
Excel MVP

 

[Jambruins]

thanks for the help, it worked perfectly and has saved me a ton of time