Lookup Multiple Worksheets

[DMcCormack]

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?

 

[Pete_UK]

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

 

[TMS]

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]

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