Using a cell to reference a particular worksheet in a workbook 1

[TOTCOM11]

In MS Excel 2003, I have 10 worksheets plus one worksheet which I call an overview sheet. Each worksheet is labeled "Summary 1", "Summary 2", Summary 3", etc... On the overview sheet I have a bunch of cells that reference other cells on these sheets. I have a single cell in cell B11 that I type the number of the worksheet whose detailed information I want to see. For instance if I want to see Summary Sheet 7, I type a "7" into the cell. My question is how can I integrate this cell into my formulas?

For Example, a cell in my sheet currently reads, ='Summary Sheet #7'!P3. I want the formula to be able to look at the box and change the "7" to anything I want. If I wanted to bring up Summary Sheet 8, it would read ='Gage Study #12'!P3. Since the layout on all the Summary Sheets are identical, I can just change the number of the summary sheet to call up the information.

Can anyone help me?

Can anyone help me?

 

[GlennUK]

Your sheet names do not appear to be consistent. The sheet name for summary 7 is:
'Summary Sheet #7'
but for summary 12 it's:
'Gage Study #12'

How many variations are there? I was thinking of using the INDIRECT function, but can't give a full answer until you've cleared my query up.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TOTCOM11]

Yeah, that was my mistake. They are SUPPOSED to be consistant. They need to all read SUMMARY SHEET #(number). There are no variations except for the actual number of the sheet.

Thanks,
TOTCOM11

 

[GlennUK]

In that case use the INDIRECT function. A typical formula will look like this:

=INDIRECT("'Summary Sheet #"&$B$11&"'!P3")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TOTCOM11]

Thanks for your valuable post! Never used the INDIRECT function before. Thanks a lot!

TOTCOM11