If you have already developed an excel spreadsheet with multiple worksheets,
and formulas that refer to cells on these multiple worksheets, how do you
change the cell references to names and make sure they are applied to each
relevant worksheet and formula

You have to do each sheet one by one, select the formula and do
insertnameapply and select the name

--
Regards,

Peo Sjoblom


"BBurrows" wrote in message
...
If you have already developed an excel spreadsheet with multiple
worksheets,
and formulas that refer to cells on these multiple worksheets, how do you
change the cell references to names and make sure they are applied to each
relevant worksheet and formula

Hi... I have been searching for an answer to this problem all day and there
seem to be no real solution.

I have a workbook with 8 worksheets that was given to me. I went through the
entire workbook and named all the important cells and cell ranges with global
names. Then I went to "apply" the names to cells with calculations where by
the cell location would be replaced with the names.

The APPLY function works just fine in replaceing cell locations with NAMES
that were created on the same page. But it does not seem to work when trying
to apply names to calculations that were created on a different worksheet
then the named range itself was created.

For example: on worksheet1 cell D7 contains =SUM(A7:C7) and is globally
name MyTotal. On worksheet2, cell C4 contains =Sheet1!D7

If I do Insert/Names/Apply/MyTotal on worksheet2, cell C4 I get the response:
"Microsoft Excel cannot find any references to replace". And if I simply
type =MyTotal in any cell on worksheet2, the proper value from worksheet1
appears.

Is there any way to replace these names other than manually?

Thanks. Mike



"Peo Sjoblom" wrote:

You have to do each sheet one by one, select the formula and do
insertnameapply and select the name

--
Regards,

Peo Sjoblom


"BBurrows" wrote in message
...
If you have already developed an excel spreadsheet with multiple
worksheets,
and formulas that refer to cells on these multiple worksheets, how do you
change the cell references to names and make sure they are applied to each
relevant worksheet and formula

After many days of trying, I have finally given up and reverted to doing it
all manually. I have all my data cells on different worksheets to my
formula cells so it has meant naming each data cell then re-doing the
formulas with the newly named data cells. With most of my formulas being
at least 4-5 lines long, I am in my second week of manually converting. I am
sorry to say I therefore still have no solution for anyone. If anyone has
any suggestions, would all be greatly appreciated.

Belinda

"Mike" wrote:

Hi... I have been searching for an answer to this problem all day and there
seem to be no real solution.

I have a workbook with 8 worksheets that was given to me. I went through the
entire workbook and named all the important cells and cell ranges with global
names. Then I went to "apply" the names to cells with calculations where by
the cell location would be replaced with the names.

The APPLY function works just fine in replaceing cell locations with NAMES
that were created on the same page. But it does not seem to work when trying
to apply names to calculations that were created on a different worksheet
then the named range itself was created.

For example: on worksheet1 cell D7 contains =SUM(A7:C7) and is globally
name MyTotal. On worksheet2, cell C4 contains =Sheet1!D7

If I do Insert/Names/Apply/MyTotal on worksheet2, cell C4 I get the response:
"Microsoft Excel cannot find any references to replace". And if I simply
type =MyTotal in any cell on worksheet2, the proper value from worksheet1
appears.

Is there any way to replace these names other than manually?

Thanks. Mike



"Peo Sjoblom" wrote:

You have to do each sheet one by one, select the formula and do
insertnameapply and select the name

--
Regards,

Peo Sjoblom


"BBurrows" wrote in message
...
If you have already developed an excel spreadsheet with multiple
worksheets,
and formulas that refer to cells on these multiple worksheets, how do you
change the cell references to names and make sure they are applied to each
relevant worksheet and formula

Oooh, that does sound painful. I have found that Find - Replace All is the best workaround. (Find the original cell reference, e.g., Sheet1!$H$9, and replace [all] with the name.) You still have to find/replace each name individually, but at least you only have to do them once.