Change link to a cell in multiple cells at once.

[Cossak]

Hey Everyone,

Here is my situation..

I have data in cell Sheet1:A1 that is referenced by 10 other cells on different worksheets. The references are all different (i.e some uses Sheet1:A1 in a formula, others are just =, some are relative references, some are fixed ad have the $).

I am changing the way the workbook works, and want all those 10 cells to now refer to Sheet 2:B2. What is the easiest way to make this change all at once?

Now I know some might say it's only 10 cells, don't be lazy...I am just using this as an example, I literally have a 1000 cells...so a short cut is very important.

Thanks,

 

[pvr928]

Hi Cossak

Three possible ways:

Easiest (but not ideal)
Cut Sheet1!A1 and paste into Sheet2!B2

Harder (ok)
Search / Replace: replace [Sheet1!A1] with [Sheet2!B2]

Hardest (best method, but sounds like not practical in this situation as it needs to be set up at the beginning)
Define a Name using Sheet1!A1 as the source and use this Defined Name in all formulae. If you want to change the source, all you have to do is change the source of the Defined Name, and the change will automatically flow.

Cheers

pvr928