Very large IF statements 2

[jds422354]

I really hope that someone can assist me with this one! I use a lot of if statements in the workbooks that i produce. I have noticed that you can only have a 7 level nested If statement if you type the formula into a cell. I am not very good with the vb in excel but i kind of think that i must be able to write the statement into a module of some kind? Below is a cut down version of the type of statement i want to write. I have a sheet called stat where i want to display cells from other sheets. The sheets are called 1,2,3,4 and 5 in the example but go up to 53 in the real thing.

=IF($E$10=1,'1'!C3,IF(stat!$E$10=2,'2'!C3,IF(stat!$E$10=3,'3'!C3,IF(stat!$E$10=4,'4'!C3,IF(stat!$E$10=5,'5'!C3,IF(stat!$E$10="all",('1'!C3+'2'!C3+'3'!C3+'4'!C3+'5'!C3),"&quot)))))


The idea behind it is that the user, (my mother bless her!) can go to the stat sheet type a number between 1 and 53 in a cell and it will display the data for that week. I hope i have explained it clearly.

many thanks

James

)

 

[xlbo]

heh heh heh - much much easier than that - use the INDIRECT function
=INDIRECT($E$10 & "!C3&quot
where E10 contains the sheet name

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ETID]

I wouls make 2 cols in the stat sheet out whre mom can't see them,... say cols AA and AB

in AA list weeks 1 to 53

in col AB type the the conditions for each week.


then use a vlookup formula for mom

say mom types 2 in cell a1, then the formula for b1 is...

=vlookup(a1,aa2:ab53,2,false)

and the vlookup returns the value declared in the formula for week 2

 

[jds422354]

Thank you both, i tried the indirect function and it works a treat. Many, many thanks to both of you for such a quick and accurate solution )