Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic referencing to files
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic referencing to files
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 "Sungibungi" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic referencing to files
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. "Jacob Skaria" wrote: 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 "Sungibungi" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic referencing to files
=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 --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic referencing to files
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 --------------- Jacob Skaria "Max" wrote: =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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing External files with slightly different names | Excel Worksheet Functions | |||
Dynamic file referencing? | Excel Worksheet Functions | |||
Creating a Dynamic .XLS file from two other .XLS files | Excel Discussion (Misc queries) | |||
Referencing Other Files - Formula Pattern? | Excel Discussion (Misc queries) | |||
Can I post dynamic line charts by swapping out .cvs files??? | Charts and Charting in Excel |