One More Try: Names/Apply on other worksheet
 

Giving this one more shot before i start to spend the next manually replacing
cell locations in formulas with names. Here's the situation:

I have 17 workbooks with 8 worksheets each that were given to me. I went
through all the entire workbooks 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 work when trying
to apply names to calculations that reference cell ranged named on different
worksheets.

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

You can only apply the names to the worksheet on which the named range
exists. There is a MSKB article (which doesn't give much more info, nor
any suggested workarounds)

http://support.microsoft.com/default.aspx?id=268974

You can use EditReplace to substitute the range name for the cell
reference.


Mike wrote:
Giving this one more shot before i start to spend the next manually replacing
cell locations in formulas with names. Here's the situation:

I have 17 workbooks with 8 worksheets each that were given to me. I went
through all the entire workbooks 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 work when trying
to apply names to calculations that reference cell ranged named on different
worksheets.

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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

You can do a Find/Replace

Find: Sheet1!D7
Replace: MyTotal

In article ,
"Mike" wrote:

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

Mike,

See this previous post from Jan-Karel Pieterse that addresses the same topic
http://tinyurl.com/8gx35

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike" wrote in message
...
Giving this one more shot before i start to spend the next manually
replacing
cell locations in formulas with names. Here's the situation:

I have 17 workbooks with 8 worksheets each that were given to me. I went
through all the entire workbooks 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 work when trying
to apply names to calculations that reference cell ranged named on
different
worksheets.

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