vlookup with changing table array

[chris86t]

i want to use vlookup to find infomation in a spreadsheet that will change name daily according to the date. Is there a way to have the table array change with the value of a cell?

here is my vlookup:

=VLOOKUP("Volume",'C:\DailySales\CD2767\[CD2767_06-01-2009.xls]Sheet1'!F1:H50,2,TRUE)

As you can see, the date is in the name of the file. I need to be able to change that date easily since there are going to be 28 similar vlookup functions. I've tried indirect, but get a ref error.

 

[Gavona]

Not sure why Indirect wouldn't work - how about posting your formula?
You could use Edit, Links, ChangeSource each day to change the referenced workbook. If Indirect doesn't work then I would use VBA to do this. To pursue this post in Forum707

Gavin

 

[SkipVought]

I've tried indirect, but get a ref error.

Yet you did not post your INDIRECT attempt.

Please do, explaining each reference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Don;t think indirect works when the other file is closed.....maybe that is the issue!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[chris86t]

my indirect function looks like this:

cell k1 ="'C:\DailySales\CD2767\[CD2767_06-01-2009.xls]Sheet1'!F1:H50"

=VLOOKUP("Volume",INDIRECT(K1),2,TRUE)

there is going to be a 14 day date range that will need to change based on K1

 

[xlbo]

Is the source file open or closed when you are trying to update the formula?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[chris86t]

It is closed.

 

[Gavona]

Geoff is, of course, correct - indirect does not work when the other file is closed.


Gavin

 

[GlennUK]

Hi chris86t,

can you open the file so that you formula works, or do you need an alternative that works with closed workbooks?

If you need an alternative then Laurent Longre has a function that works that way in his free MOREFUNC add-in, named INDIRECT.EXT ( indirect for external references ). See here to download the add-in:

http://xcell05.free.fr/morefunc/english/#Download

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[chris86t]

There are 14 files I am pulling from, so I can't open them all the time. I already have morefunc installed. What would I use with morefunc?

 

[xlbo]

As Glenn has already stated - the INDIRECT.EXT function...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PeterMoran]

Hi,

Glenn's URL for Morefunc does not appear to be working - probably for some time!

Any other places this Addin can be located?

Regards,

Peter Moran

 

[GlennUK]

oh dear, Laurent Longre's site does seem a bit derelict once you try to do anything on there. Sorry, it's been a while since I last downloaded from there. The forum is down as well, so I couldn't even ask Lauren about the broken download link.

chris46t ... hopefully as you've already got morefunc you should be OK.

Cheers, Glenn.

Beauty is in the eye of the beerholder.