relative references to absolute references

Hello! I have a destination worksheet that reads every single cell from source worksheet. The idea is that if you make updates to the source sheet, they show on the destination sheet.

Of course, to accomplish this, I pasted a relative reference, because an absolute reference gave me cell A1 only, and everywhere.

I now have 40,000 cell references that are relative - and need to be absolute. Is there a way to accomplish this outside search and replace - I'm not even sure how I would manage search and replace for this one.

Does anyone have a solution?

Thank you so much!

You can use Find & Replace (CTRL-H) like this:

suppose your formulae are like this:

=Stock!C2, =Stock!C3, and so on. Then you can:

Find what: !C

Replace with: !$C$

then click on Replace All.

Note that the !C ensures that the "c" in the sheet name does not get changed, and if you only want to make the row reference absolute, then you can:

Replace With: !C$

You may have to apply this a few times, depending on how many different columns are used in your formulae.

Hope this helps.

Pete

Hi Pete, thanks so much!I have SO many formulae...lol. Fortunately, as we often see, my boss scrapped the entire idea.

Okay, well maybe you can remember that method if the boss resurrects the idea in the future.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete