Sheet Referencing within one Workbook

[Greg K]

Hi,
I am creating a workbook that is keeping track of hours worked on several different construction jobs. For each new job, I plan to have a new Sheet that matches a template. I have the sheets calculating the hours separately, and there are no problems there.
The issue I'm having is I want a Master Log at the beginning of the file that references the names of each sheet, and the hours that are calculated on said sheet. Where I hit a problem is when referencing the sheets; I don't know what the sheets will be named, and I don't know how many sheets I'll have, so I want to create a template that simply looks up the name of the Sheet, and then references that sheet to find the number of hours logged to that job.
I am willing to use VBA, or regular formula's.
Please help!
- Greg

[JohnTopley]

formulae of this type

=INDIRECT("'" & X1 &"'!A1") gets A1 value from sheet named in X1


=SUM(INDIRECT("'"&X1&"'!A1:A10")) sums A1:A10 for data in sheet named in X1

where X1 = Sheet_Name

[Greg K]

And will this work if I don't have the name of the sheet? I want to have this where I can put in a formula and change the number in the formula, and it references the next sheet. I don't want to have to format the Master Log each time a new job comes in.

[Greg K]

formulae of this type

=INDIRECT("'" & X1 &"'!A1") gets A1 value from sheet named in X1


=SUM(INDIRECT("'"&X1&"'!A1:A10")) sums A1:A10 for data in sheet named in X1

where X1 = Sheet_Name

Not sure if you were tagged in that reply. See below.

[JohnTopley]

Do you mean ??

=INDIRECT("'SheetName" & X1 &"'!A1") gets A1 value from sheet number in X1

So tabs names are "Sheetname1", "Sheetname2" etc and X1 will contain 1, 2 etc

If you don't have a sheet name then it's "no go"!

Sheet names with prefix or suffix is the easiest way. Alternatively you have a range of cells containing the sheet names and do a "lookup" of some kind