• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing cell reference

A

a_dani20

New Member
hi guys.. i have one small problem i am using a particular formula which contains reference to particular cell ( say A1) several times ( 7-8 times) now i want to copy that formula to another sheet with reference to another cell ( say b7). For this i have to manually change all references from A1 to B7. i was wondering is there is any way by which it can be done faster?


Thanks in Adv
 
Hi ,


I think one way is to copy the formula to any unused cell , and insert an apostrophe ( ' ) at the beginning of the formula , before the '=' sign. This converts the entire formula to text.


Now select this cell and press CTRL H to bring up the Find and Replace dialog box. Type in A1 in the Find box , and B7 in the Replace box , and select replace all.


Remove the inserted apostrophe sign , and copy the changed formula to whichever cell you want.


Narayan
 
A_dani20

Just select that cell and another cell which doesn't have A1 in it or is blank

Do an ordinary Find/Replace

Excel won't search outside the 2 cells you have selected
 
Thanks Hui.


I find that even selecting an additional cell is not required ! Just selecting the cell with the formula and doing a Find and Replace works. I didn't think this would work.


Narayan
 
Narayank991,


True, that will change the 1 cell, but if you don't select a range, the Find & Replace might go affect other cells as well. (Of note, make sure the Find & Replace options is set to look in Sheet, not Workbook).
 
You must log in or register to reply here.
Back
Top