Long IF statement

[guerillaexcel]

I have a validation that I use to select a month, this in turn defines what gets returned in a formula.

Example:

Please Login or Register  to view this content.

This does work but as I want to do include every month its far from elegant, is there a better method?
Cheers

[snb]

=IF(INDIRECT(G1 & "!$B8")=0,indirect(G1 & "!D8"),"")  

[zbor]

Here, try this:

=IF(INDIRECT(G1&"!B8")=0, INDIRECT(G1&"!D8"), "")

[guerillaexcel]

fantastic lads, great minds there!! 1 minute apart
Can I ask what the formula actually does.

[guerillaexcel]

Oh and is it possible to drag and copy this formula to rows below?

[snb]

Yes you can, but in that case you probably need:

=IF(INDIRECT($G$1 & "!$B8")=0,indirect($G$1 & "!$D8"),"")  

[guerillaexcel]

Thanks snb, but this does not appear to work, "!$B8" doesn't change as I drag the cell to copy into the rows below, likewise with "!$D8". Any thoughts or am I doing something really stupid here?

[batman07]

I think snb is offline right now, maybe we could help if you upload a sample your excel file.

[Domski]

Maybe:

=IF(INDIRECT($G$1 & "!$B" & ROW(A8))=0,INDIRECT($G$1 & "!$D" & ROW(A8)),"")

Dom

[zbor]

try this:

=IF(INDIRECT($G$1&"!$B"&ROWS($B$1:$B8))=0,INDIRECT($G$1&"!$D"&ROWS($B$1:$B8)),"")

ROWS($B$1:$B8) is number 8
If you pull it down you get 9,10 etc

For changing first number you need to adjust it.

[guerillaexcel]

sure, see attached.
Thanks

[guerillaexcel]

BOOM! thats done the trick zbor, thank you very much!
And Domski, your solution works equally well, thank you thank you!