if then statements referencing multiple worksheets

[gretti24]

I am trying to come up with a formula that states: if cell A1 = "January" then go to the January worksheet and grab cell B15 from the worksheet. If cell A1 = "February" go to the February worksheet and grab the data.

I've tried to do it by developing multiple nests but for a one year period is reached the maximum fairly quickly.

I am intermediate I'd say with excel so creating a vba I'd like to avoid.

I was also told that maybe going with a concatenate formula could possible work, but I haven't had any luck with that.

Any help would would greatly appreciated!

 

[Gavin T]

You can use this formula:

=INDIRECT(A1 & "!B15")

 

[XOR LX]

Hi,

=INDIRECT("'"&A1&"'!B15")

Regards

 

[gretti24]

Okay this is good...but what if i need a range for it to pull in multiple rows and columns of data that are in a table from the other tab. Both table are identical in each worksheet so and the forumla needs to work so that I can drag down and across and reflect the worksheet that is indicated in cell A1...

So in this case i want to drag the formula down so it would be something like: =INDIRECT("'"&A1&"'!B16") ...B17.. .B18
and also drag across columns?

 

[Gavin T]

You can use the following formula:

=INDIRECT("'" & $A$1 & "'!" & CELL("address",B15))

Thanks for bringing up an interesting question!

 

[gretti24]

You can use the following formula:

=INDIRECT("'" & $A$1 & "'!" & CELL("address",B15))

Thanks for bringing up an interesting question!

Works great!

Saved me a great deal of time and headaches! Thanks.

 

[Gavin T]

...Thanks.

You're welcome!