Lookup Information from Across Worksheets

D

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!
 
S

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.
 
J

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.
 
D

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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top