[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Change Cell Formula References (All at once)

Posted on 2011-05-09
Medium Priority
237 Views
Experts,

I have a large spreadsheet and need to chagne the formula references of a sheet from the already referenced sheet to another sheet in the Workbook.

How can I do this all at once?  (I know I can cntrl F for Find and then replace but I believe there to be an easier way).

thank you
0
Question by:pdvsa
• 8
• 5
• 3
• +2

LVL 15

Expert Comment

ID: 35723183
can you provide an example for us to update?
0

Author Comment

ID: 35723256
Jocasio:  I can not do this right now.  It might be better to explain if possible.
0

Author Comment

ID: 35723279
If I have several thousand formulas with this:
=VLOOKUP(B39,'LC Database-JPM'!\$A\$21:\$W\$160,22,FALSE)

How do I change the name of the workbook for ALL the formulas?
0

LVL 33

Accepted Solution

jppinto earned 1000 total points
ID: 35723466
You can use Ctrl+H (Find and Replace) function. Take a look at the attached image.

jppinto
Capturar.JPG
0

LVL 85

Expert Comment

ID: 35723603
Ctrl+H is the easiest way if the formulas are already in place. Alternatively you could use INDIRECT functions but I wouldn't advise it due to its being a volatile function.

As an aside, a VLOOKUP function referring to 22 columns when you are only interested in 2 is not particularly efficient. INDEX and MATCH would be better.
0

Author Comment

ID: 35723664
rorya:  what do you mean by this:
<As an aside, a VLOOKUP function referring to 22 columns when you are only interested in 2 is not <particularly efficient. INDEX and MATCH would be better.
0

LVL 33

Expert Comment

ID: 35723713
Did you tryed to do the Find and Replace like I told you? Does that work for you?
0

Author Comment

ID: 35723733
jppinto:  yes this will work but I thought there was a better way to do this.  I did allude to this in the original question: (I know I can cntrl F for Find and then replace but I believe there to be an easier way).
0

LVL 42

Expert Comment

ID: 35723756
If you're changing ALL references from one workbook to another, I recommend UPDATE LINKS.  Find/Replace will work, but you'll run into all kinds of problems if you mis-type the filename - and if its in a different directory, that can be a challenge, as well.

Shortcut keys to get there:

Alt-E  then hit K.  From Excel 2003, that's Edit, Links.  Select the workbook in question, then CHANGE SOURCE.

Alternatively, from Excel 2007, Click the Office Button at the top-left hand of Excel, then PREPARE, then Edit LINKS to FILES.  Finally, select the workbook in question, then CHANGE SOURCE.

Cheers,

Dave
0

LVL 33

Expert Comment

ID: 35723767
Better that this?!? This is the simplest way of doing this! You don't need to spend no even a minute writing code to make a macro when you can use the functions of Excel to do the same is 30 seconds or less!
0

Author Comment

ID: 35723852
I was just a little hesitant doing it that way.  I see now that this is the best way.

thanks .
0

Author Comment

ID: 35723858
oh wait I see Dave responded.
0

LVL 42

Expert Comment

ID: 35723862
I'm not sure I agree.  You might also explore changing workbook links - however, I understand if you're not changing ALL references to the subject workbook.  If you are, I believe updating links to be superior for the reasons stated.

Dave
0

Author Comment

ID: 35723891
Dave:
<Alternatively, from Excel 2007, Click the Office Button at the top-left hand of Excel, then PREPARE, <then Edit LINKS to FILES.  Finally, select the workbook in question, then CHANGE SOURCE.
==>I do not see the Edit Links to Files... let me know what I did wrong.  FYI:  I am changing the sheet name to another sheet in the same workbook.
untitled.JPG
0

LVL 42

Expert Comment

ID: 35723932
If you have a workbook open that is doing a VLOOKUP against another Workbook, then you SHOULD see the option to EDIT LINKS in that menu you show.

If you don't have a workbook open that's referencing another workbook, then that option does not appear.

Try it with your workbook that has the VLOOKUPS to the other workbook.

Dave
0

LVL 42

Expert Comment

ID: 35723940
I have to run to a meeting, but will respond to additional questions (if any) along this path of updating links, when I return.  Est 1 hour.

Dave
0

LVL 85

Assisted Solution

Rory Archibald earned 1000 total points
ID: 35724006
Since you are talking about sheets in the same workbook, updating links is not an option. Find/Replace is your best bet.
0

Author Comment

ID: 35724067
OH I see.  The other way is good to know too.  thank you
0

LVL 42

Expert Comment

ID: 35724117
That explains it.

Dave
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month19 days, 5 hours left to enroll