cannot find any references to replace

Hi there,

I have a work problem that has now become personal, if I don't beat it I don't think I will be able to sleep!

I have a workbook with various sheets, each sheet is used to hold data. I then have a calculation sheet that uses the data in the various sheets and performs calculations on them. The formulas in the calculation sheet are so long a arduous that testing is so difficult. I then learned that you can name cells, so that instead of having Sheet1!$A$1 you can have a word like (Connection Fee).

I have read so much in the Help files on how to use cell references after the formulas have been created, but when I try to apply the cell references (names) to my calculation sheet I get the error:

Microsoft Excel cannot find any references to replace.

I would be eternally greatful for any help here...as I am running out of coffee!

I'm no excel guru, in fact I just registered because I needed help and stumbled across this site.

However, the error you're posting seems to strongly suggest that the search half of the search & replace action you're attempting isn't finding anything.

I'm looking at the Edit-Replace dialog box right now, and just based on that...

You don't have "Match entire cell contents" checked do you?

No I just need to replace the reference to a cell in a formula to a name.

Insert> Name> Apply.

If it's a limited number of cells that require the replacement, you should be able to copy the formula out to like notepad, do the replace there, then paste the new formula in.

Will that work or am I way off base here?

Thanks airjdn,

If there where only a few formulas that would be great, but I have about 600 cells with formulas that contain up to 20 cell references each. So copying out to a text file could be risky if not done properly.

It seems like a pretty simple request but I am really struggling to find any help on the Microsoft site etc. V frustrating.

Thanks for your help, I notice that my issue is similar to yours, except yours is to do with Hyperlinks and mine formulas... maybe some lovely excel wizard will come along and make everything better for us both... fingers crossed!