Can you auto edit file names inside a formula

[cedric.dennis]

Hi
I have a main excel file created yearly eg rate2010.xls
The values in this file change year on year basis or sometimes even
earlier.

I have another file pricing.xls which uses values from the file
rate2010.xls
The values in pricing.xls are picked up from rate2010.xls by the
formula function Vlookup

My problem:
---------------------------------------------------------------------------------------------------------------------------
When the year changes; I have to make a new file
i.e rate2010.xls becomes rate2011.xls

The file pricing.xls still continues to pick up values from the old
rate2010.xls.
I want it to pick up values from the new file rate2011.xls

-----------------------------------------------------------------------------------------------------------------------------
My Question:
Can I change the ref filename from rate2010.xls to rate2011.xls in the
formula bar automatically
in my file pricing.xls ?

I have thousands of entries which need changing ...manual is very
tedious.


Please help if you can.

Warm regards
Cedric Dennis

 

[Gord Dibben]

Edit>Links>Change source



Gord Dibben MS Excel MVP

 

[Jim Cone]

Use the "Replace" menu item on the Edit menu.

Find What... 2010.xls
Replace with... 2011.xls
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/XLCompanion

..
..
..

<[email protected]>
wrote in message
Hi
I have a main excel file created yearly eg rate2010.xls
The values in this file change year on year basis or sometimes even
earlier.

I have another file pricing.xls which uses values from the file
rate2010.xls
The values in pricing.xls are picked up from rate2010.xls by the
formula function Vlookup

My problem:
---------------------------------------------------------------------------------------------------------------------------
When the year changes; I have to make a new file
i.e rate2010.xls becomes rate2011.xls

The file pricing.xls still continues to pick up values from the old
rate2010.xls.
I want it to pick up values from the new file rate2011.xls

-----------------------------------------------------------------------------------------------------------------------------
My Question:
Can I change the ref filename from rate2010.xls to rate2011.xls in the
formula bar automatically
in my file pricing.xls ?

I have thousands of entries which need changing ...manual is very
tedious.


Please help if you can.

Warm regards
Cedric Dennis