Dynamic referencing to files

[Sungibungi]

Hello users.

I'm trying to figure out a way to dynamically reference file names that have
different months. I am trying to do the following:

There is a master file and many slave files (organized into months).
Master.xls wants to vlookup data corresponding to date from corresponding
month file.

Example:
A B
1/1/2009 --> Vlookup from "Jan 09.xls" column one to find 1/1/2009 and
return Column B

Inside Master.xls file, I extracted each file extension ("Jan 09", "Dec 10",
etc) in column Z to reference.

So then I used this formular to start the dynamic referencing:

=vlookup($A1,INDIRECT("'[C:\...\"&$Z1&".xls]Sheet1'!A:B"),2,0)

This results in a #REF but it seems the
INDIRECT("'[C:\...\"&$Z1&".xls]Sheet1'!A:B") segment of the formular should
work fine. I'm not sure. Am I missing something? Could someone help me out
here?

Many thanks in advance.

 

[Jacob Skaria]

The workbook should be open..You can download an add-in called Morefunc which
has a function called INDIRECT.EXT that will work if the source book is closed

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes

 

[Sungibungi]

Thanks for the solution Jacob. I really appreciate it. It had more to do with
the way I referenced the file.

I have an additional question for you, if you can help me.

It seems like a simpler function but I can't get it to work.

The scenario is basically the same as described before but referencing to
another sheet (Ex. - Sep 09 Update), not another file.

So this is the formular I came up with.

=VLOOKUP($A1,INDIRECT(Z1&" Update!A:B"),2)

It still gives me a #REF error. Is my sheet referencing off?

Once again, thanks for your help.

The workbook should be open..You can download an add-in called Morefunc which
has a function called INDIRECT.EXT that will work if the source book is closed

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria

Hello users.

I'm trying to figure out a way to dynamically reference file names that have
different months. I am trying to do the following:

There is a master file and many slave files (organized into months).
Master.xls wants to vlookup data corresponding to date from corresponding
month file.

Example:
A B
1/1/2009 --> Vlookup from "Jan 09.xls" column one to find 1/1/2009 and
return Column B

Inside Master.xls file, I extracted each file extension ("Jan 09", "Dec 10",
etc) in column Z to reference.

So then I used this formular to start the dynamic referencing:

=vlookup($A1,INDIRECT("'[C:\...\"&$Z1&".xls]Sheet1'!A:B"),2,0)

This results in a #REF but it seems the
INDIRECT("'[C:\...\"&$Z1&".xls]Sheet1'!A:B") segment of the formular should
work fine. I'm not sure. Am I missing something? Could someone help me out
here?

Many thanks in advance.

 

[Max]

=VLOOKUP($A1,INDIRECT(Z1&" Update!A:B"),2)

I'd always include the possibility of target sheetnames having spaces.
Try it like this, with apostrophes:
=VLOOKUP($A1,INDIRECT("'"&Z1&" Update'!A:B"),2)
Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Jacob Skaria]

Thanks Max for the follow up..

--Just to add on if 'Sep 09' is a text string entered in cell Z1 try the
formula Max offered. If you want an exact match try
=VLOOKUP($A1,INDIRECT("'"&Z1&" Update'!A:B"),2,0)

--Incase that is a date formatted to view as Sep 09 then you

=VLOOKUP($A1,INDIRECT(TEXT(Z1,"""'""mmm yy"" Update'!A:B""")),2,0)


If this post helps click Yes