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
> After many days of trying, I have finally given up and reverted to doing
> 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
> 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
> any suggestions, would all be greatly appreciated.
> "Mike" wrote:
> Hi... I have been searching for an answer to this problem all day and
> 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
> 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
> that were created on the same page. But it does not seem to work when
> to apply names to calculations that were created on a different
> then the named range itself was created.
> For example: on worksheet1 cell D7 contains =SUM(A7:C7) and is
> name MyTotal. On worksheet2, cell C4 contains =Sheet1!D7
> If I do Insert/Names/Apply/MyTotal on worksheet2, cell C4 I get the
> "Microsoft Excel cannot find any references to replace". And if I
> type =MyTotal in any cell on worksheet2, the proper value from
> 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
> Peo Sjoblom
> "BBurrows" BBurrows@discussions.microsoft.com wrote in message
> If you have already developed an excel spreadsheet with multiple
> 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-