Lookup Information from Across Worksheets

[da77]

I have a Workbook with monthly worksheets (tabs) (i.e. JAN, FEB, MAR..., DEC)

On another sheet, I will have a cell referencing which month I will generate
a report for.

Using that cell, without using nested if statements, can I have Excel
reference data in that spciific month's tab for my formulas in the report?

I may not be clear in the ablve, please let me know if I need to clarify.

Thanks!

 

[Spiky]

I have a Workbook with monthly worksheets (tabs) (i.e. JAN, FEB, MAR..., DEC)

On another sheet, I will have a cell referencing which month I will generate
a report for.

Using that cell, without using nested if statements, can I have Excel
reference data in that spciific month's tab for my formulas in the report?

I may not be clear in the ablve, please let me know if I need to clarify.

Thanks!

You can use INDIRECT in your formulas to create usable cell references
from text. Have the cell with the month name match the names of the
sheets/tabs. So, if A1 has "JAN" in it and you want to sum from the C
column on sheet JAN:
=SUM(INDIRECT(A1&"!C2:C100"))

Or you can define some named ranges, which can also be used with
INDIRECT.

 

[John C]

Let's say you have a summation sheet with the months listed down column
A2:A13. Let's also assume that your months in this list exactly match the
worksheet names for each month.
B2: =INDIRECT(A2&"!F20)
copy down to B13, and this will populate cells B2:B13 on your summary tab
with the value in cell F20 for each respective month tab.

Read up more on the indirect tab, as well as VLOOKUP if you are looking for
specific information on each tab. You can search the groups here as well,
there are plenty of examples.

Hope this helps.

 

[Dave Peterson]

It's usually a good idea to surround the worksheet with apostrophes--if they're
not needed, then there won't be any harm.

And if they are needed, you have them:

=indirect("'" & a1 & "'!a1")