Change sheet name in formula
 

I use the folowing formula in a summary sheet that looks at specific cells on
other work sheet. =-1*SUM('5-4'!$O$41:$O$45) The worksheets are named 5-1,
5-2, etc. on my summary sheet I have a column that contains the work sheet
names formatted as text. I tried substituting a cell reference and various
text functions for '5-4' with and without the ' ' but when I do, Excel tries
to open a file. How can I make the formula look at the same cells on
different woorksheet without having to edit each formula or pointing to the
cells each time I ad a new worksheet?

If all of the worksheets are in the same workbook, try using the INDIRECT function (refer to
in-built help for syntax).
Rgds,
ScottO

"kojimm" wrote in message
...
| I use the folowing formula in a summary sheet that looks at specific cells on
| other work sheet. =-1*SUM('5-4'!$O$41:$O$45) The worksheets are named 5-1,
| 5-2, etc. on my summary sheet I have a column that contains the work sheet
| names formatted as text. I tried substituting a cell reference and various
| text functions for '5-4' with and without the ' ' but when I do, Excel tries
| to open a file. How can I make the formula look at the same cells on
| different woorksheet without having to edit each formula or pointing to the
| cells each time I ad a new worksheet?

Use

=-1*SUM(INDIRECT("'"&A1&"'!$O$41:$O$45"))

where A1 holds the sheet name.

--
HTH

Bob Phillips

"kojimm" wrote in message
...
I use the folowing formula in a summary sheet that looks at specific cells
on
other work sheet. =-1*SUM('5-4'!$O$41:$O$45) The worksheets are named
5-1,
5-2, etc. on my summary sheet I have a column that contains the work
sheet
names formatted as text. I tried substituting a cell reference and
various
text functions for '5-4' with and without the ' ' but when I do, Excel
tries
to open a file. How can I make the formula look at the same cells on
different woorksheet without having to edit each formula or pointing to
the
cells each time I ad a new worksheet?

Thank You Very Much!!!

"Bob Phillips" wrote:

Use

=-1*SUM(INDIRECT("'"&A1&"'!$O$41:$O$45"))

where A1 holds the sheet name.

--
HTH

Bob Phillips

"kojimm" wrote in message
...
I use the folowing formula in a summary sheet that looks at specific cells
on
other work sheet. =-1*SUM('5-4'!$O$41:$O$45) The worksheets are named
5-1,
5-2, etc. on my summary sheet I have a column that contains the work
sheet
names formatted as text. I tried substituting a cell reference and
various
text functions for '5-4' with and without the ' ' but when I do, Excel
tries
to open a file. How can I make the formula look at the same cells on
different woorksheet without having to edit each formula or pointing to
the
cells each time I ad a new worksheet?

Thank You Very Much!!!

"ScottO" wrote:

If all of the worksheets are in the same workbook, try using the INDIRECT function (refer to
in-built help for syntax).
Rgds,
ScottO

"kojimm" wrote in message
...
| I use the folowing formula in a summary sheet that looks at specific cells on
| other work sheet. =-1*SUM('5-4'!$O$41:$O$45) The worksheets are named 5-1,
| 5-2, etc. on my summary sheet I have a column that contains the work sheet
| names formatted as text. I tried substituting a cell reference and various
| text functions for '5-4' with and without the ' ' but when I do, Excel tries
| to open a file. How can I make the formula look at the same cells on
| different woorksheet without having to edit each formula or pointing to the
| cells each time I ad a new worksheet?