Change link to a cell in multiple cells at once.

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Change link to a cell in multiple cells at once.

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change link to a cell in multiple cells at once.

     
    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,

  2. #2
    Board Regular
    Join Date
    Oct 2002
    Location
    Sydney, Australia
    Posts
    702
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change link to a cell in multiple cells at once.

      
    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 by pvr928; Dec 1st, 2017 at 12:44 AM.
    Work: Excel 2010 64 bit
    Home: Excel 2016 64 bit

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com