Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Information from Across Worksheets
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Information from Across Worksheets
On Nov 7, 2:51*pm, da77 wrote:
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Information from Across Worksheets
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. -- ** John C ** "da77" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Information from Across Worksheets
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") da77 wrote: 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! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compute using information from two different worksheets ! | Excel Worksheet Functions | |||
Lookup information in Access Database | Excel Discussion (Misc queries) | |||
information the same for cells on two different worksheets? | Excel Discussion (Misc queries) | |||
How do I move information between worksheets? | Excel Worksheet Functions | |||
lookup information in a row like a record | Excel Worksheet Functions |