VLOOKUP auto-update

[crmorgan]

Hi, I need to create a file that updates weekly with the corresponding week (i.e. File: 04-04-09abc will update with file 04-04-09 and the same will happen with File: 04-11-09abc. It will update with file 04-11-09)

Is there a way that I can have the files link each week without having to redo all the VLOOKUP formulas for each week?

 

[Gavona]

Edit, Links, ChangeSource.
Make sure the file you are linking to is open if there are lots of formulae.

OR with the filename in cell D4 and using named ranges for your lookup range:
=VLOOKUP($A5,INDIRECT($D$4&".xls!mylook"),2,0)

Get that working and probably you could do away with D4 and put it all in the formulae.



Gavin

 

[Gavona]

Sorry, I meant to say. Indirect will ONLY work if the linked workbook is open.

Gavin

 

[yogia]

Hi crmorgan:

I suggest you clarify the following ...

1) what do you mean by updating 'File: 04-04-09abc will update with file 04-04-09'?

2) are the various files that you reference in your post ...worksheets of the same workbook, or are these in different workbooks?



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[crmorgan]

Hi Yogi,

I hope this clarification works.

I have two files of different workbooks. One file is titled "04-04-09" and the other is titled "04-04-09abc" I used VLOOKUP to populate the cells in "04-04-09abc" with the data that is in "04-04-09".

Thanks for your help!

 

[GlennUK]

It's just like Gavona ( Gavin ) said then

Edit, Links, ChangeSource.
Make sure the file you are linking to is open if there are lots of formulae.

Cheers, Glenn.

Beauty is in the eye of the beerholder.