Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Worksheets
I have a workbook with multiple sheets each representing a different day of
the month and containg data relating to the days sales in columns of locations and amounts. I want to create a summary sheet with locations down column a and dates across row 1 and have to sales by location by day on one sheet. Is there a way to lookup the sheets without having to change the sheet name in each formula? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Worksheets
Yes, you can do it using INDIRECT to form the sheet name and range,
assuming your names have some naming consistency. Perhaps if you post back with actual details of cells/sheets used and how things are laid out in your summary sheet, then we might be able to give you a formula to achieve this. Pete On Sep 29, 5:27*pm, DMcCormack wrote: I have a workbook with multiple sheets each representing a different day of the month and containg data relating to the days sales in columns of locations and amounts. I want to create a summary sheet with locations down column a and dates across row 1 and have to sales by location by day on one sheet. Is there a way to lookup the sheets without having to change the sheet name in each formula? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Worksheets
Well, since the original poster didn't reply, I guess I will.
I have my data much the same, except that how I have it is in monthly sales, and the monthly net sales amount I'm wanting to summarize in a "summary sheet" (or a table maybe or something) is found on each of twelve monthly worksheets for each year. That sama amount for each month can be found in my H21 cell in each worksheet. Thank you so much for this answer to a question that's been bugging me for a while now. I'll check back here, and I do have the "notify me of replies" box checked here below. "Pete_UK" wrote: Yes, you can do it using INDIRECT to form the sheet name and range, assuming your names have some naming consistency. Perhaps if you post back with actual details of cells/sheets used and how things are laid out in your summary sheet, then we might be able to give you a formula to achieve this. Pete On Sep 29, 5:27 pm, DMcCormack wrote: I have a workbook with multiple sheets each representing a different day of the month and containg data relating to the days sales in columns of locations and amounts. I want to create a summary sheet with locations down column a and dates across row 1 and have to sales by location by day on one sheet. Is there a way to lookup the sheets without having to change the sheet name in each formula? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Multiple Worksheets
Suppose you list the sheet names that you use in column A of your
summary sheet like this: Jan Feb Mar and so on, then in B1 you could have this formula: =INDIRECT(A1&"!H21") and then just copy this down to row 12. It will fetch H21 from each sheet in turn. If you have spaces in your sheet names, like Jan 08, Feb 08 etc, then you will have to use: =INDIRECT("'"&A1&"'!H21") as the sheet name will have to be enclosed by apostrophes. Hope this helps. Pete On Dec 6, 12:46*am, TMS wrote: Well, since the original poster didn't reply, I guess I will. I have my data much the same, except that how I have it is in monthly sales, and the monthly net sales amount I'm wanting to summarize in a "summary sheet" (or a table maybe or something) is found on each of twelve monthly worksheets for each year. That sama amount for each month can be found in my H21 cell in each worksheet. Thank you so much for this answer to a question that's been bugging me for a while now. I'll check back here, and I do have the "notify me of replies" box checked here below. "Pete_UK" wrote: Yes, you can do it using INDIRECT to form the sheet name and range, assuming your names have some naming consistency. Perhaps if you post back with actual details of cells/sheets used and how things are laid out in your summary sheet, then we might be able to give you a formula to achieve this. Pete On Sep 29, 5:27 pm, DMcCormack wrote: I have a workbook with multiple sheets each representing a different day of the month and containg data relating to the days sales in columns of locations and amounts. I want to create a summary sheet with locations down column a and dates across row 1 and have to sales by location by day on one sheet. Is there a way to lookup the sheets without having to change the sheet name in each formula?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Multiple Worksheets/Workbooks | Excel Discussion (Misc queries) | |||
lookup function for multiple worksheets...and then 'some' | Excel Worksheet Functions | |||
How can use a lookup formula through multiple worksheets? | Excel Worksheet Functions | |||
Lookup across multiple worksheets | Excel Worksheet Functions | |||
Lookup across multiple worksheets | Excel Worksheet Functions |