Home |
Search |
Today's Posts |
#1
|
|||
|
|||
apply cell names to formulas in multiple worksheets
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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.
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I link a cell in one worksheet to a cell in another works. | Excel Worksheet Functions | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Can I link a cell to reflect a worksheet name? | Excel Discussion (Misc queries) | |||
Cell Formats in formulas | Excel Discussion (Misc queries) |