[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
19
Medium Priority
?
237 Views
Last Modified: 2012-05-11
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
Comment
Question by:pdvsa
  • 8
  • 5
  • 3
  • +2
19 Comments
 
LVL 15

Expert Comment

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

Author Comment

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

Author Comment

by:pdvsa
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Accepted Solution

by:
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

by:Rory Archibald
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

by:pdvsa
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

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

Author Comment

by:pdvsa
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

by:dlmille
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

by:jppinto
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

by:pdvsa
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

by:pdvsa
ID: 35723858
oh wait I see Dave responded.
0
 
LVL 42

Expert Comment

by:dlmille
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

by:pdvsa
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

by:dlmille
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

by:dlmille
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

by:Rory Archibald
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

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

Expert Comment

by:dlmille
ID: 35724117
That explains it.

Dave
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question