Change sheet number in formula

[Gary Whittaker]

Is there a way to do the following. I have a sheet with a formula that
reference to another sheet. I need to pull a number from the same cell
in every sheet.

I want to do a summary of some sheets using the formula...
=+'Sheet1'!A1
=+'Sheet2'!A1

Instead of hard coding the sheet number in the above formula, is there a
way to reference a sheet number from another cell.??

ie
Cell A1 = Sheet1"
Cell A2 = "Sheet2"

Then have another cell that pulls from A1
=+'CellA1'!A1

Very difficult to explain.... hope that made some sense...

Thanks


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Peterson]

I think you want =indirect()

With the sheet name in column A,

=indirect("'" & a1 & "'!a1")

Also, excel allows, but doesn't require that + in your formula.

The single quotes are required for some worksheet names (sheets that are named
as numbers, sheet names that include spaces). But they won't hurt ever.