You are here:

Excel/Redirecting cell links

Advertisement


Question
[Excel 2010]

Hi Tom,

I hope you can help me with a puzzle?

I would like to be able to redirect the dependent links of a cell onto another cell.

Let me illustrate the point. Say I have two cells, G15 and G10. Both of these have a number of dependent cells using them as inputs. At this point I want the output of G15 and G10 to be the same (driven by G10) so I set G15 '=G10'. Is there a way of removing this duplication and pointing all the dependent relationships of G15 onto G10 (while not overwriting G10's dependent relationships).

I hope the question makes sense! Let me know if it needs clarifying.

Thank you very much,
Alex.

Answer
Alex,

If I understand correctly, you might have a formula in any cell that is

=G15*F22 and you want to change it to =G10*F22  and you want to do this type of change for all formulas that contain G15.  

If so then

select the range of cells where you want to make this type of change (you can include cells that have unrelated content - it will only affect the cells with formulas matching the condition).  

On the right side of the Home tab go to Find and select.  click the dropdown for the item and select Replace . . .

In the dialog, in the Find What box put   G15
in the Replace With box put  G10

now G15 could look like $G$15, $G15, G$15, G15.  If that is the case, you would need to do this action for each type that is in your range.  

This further assumes that if G15 is in the formula of a cell, you want to replace it with G10.  

In the Replace dialog, you can choose the options button.  You want to make sure that you have selected to Look in: Formulas as opposed to values.  Also "Match entire cell contents" is unchecked.  

I believe this is the technique you are looking for.

If you execute this command and things go wrong, you try undo or close your workbook without saving.  (so save your workbook before you start).

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.