Change Cell Formula References (All at once)

can you provide an example for us to update?

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

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?  

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.

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.

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

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).

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

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!

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

thanks .

oh wait I see Dave responded.

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

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

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

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

OH I see.  The other way is good to know too.  thank you

That explains it.

Dave