Change link to a cell in multiple cells at once.

Cossak

New Member
Joined
Nov 30, 2017
Messages
1
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,
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top