Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
Reference Data in Moved Worksheet | Setting up and Configuration of Excel |