vlookup using path creation

[Corbana]

ok its hard to explain what i am trying to do but here goes:

range names in { }
forumlas in ** **

[book1.xls]

--------1-----------------------------------2
A 11/11/2005 **=TODAY()** -----051111 ** =TEXT($B$2,"yymmdd")**
B 'c:\test\ {path}
C 051111.xls {filename} **=B2&".xls"
D 'c:\test\[051111.xls]'!$A$1:$J$10 {ranged} ** =path&"["&filename&"]"&"!$A$1:$J$10" **

F 123456
G **=vlookup(F1,ranged,1)**

[051111.xls]

A1 = 123456
B1 = 1
C1 = 3
D1 = 9
E1 = 15

ok the problem is that it is using the range "{ranged} in book1.xls for the vlookup and i want it to use the path i have in that cell.

anyone able to offer assistance.

 

[PaddyD]

review the help file entry for indirect()

 

[Corbana]

tried using =indirect(d1)

and jsut comes dack with an #name error

 

[PaddyD]

"ok its hard to explain what i am trying to do but here goes"

Review the html maker faq thread at the top of this board, then use the addin to post up a snapshot of your sheet that illustrates what you're trying to to.

 

[Corbana]

this is book1:

book1.xls
	A	B	C	D	E
1	Today	Yesterday	as text
2	16/11/2005	15/11/2005	051115
3
4	'C:\test\	< Range path
5	051115.xls	< Range filename
6	'C:\test\[051115.xls]'!$A$1:$J$10			< Range ranged
7
8	123456
9	#N/A	<=vlookup(A8,ranged,2)
10
11	#REF!	<=INDIRECT(ranged)
12
Sheet1

 

[Corbana]

and this is 051115

051115.xls
	A	B	C	D	E
1	123456	1	3	9	15
Sheet1

 

[barry houdini]

INDIRECT only works if the workbook you are referencing is open, in which case you shouldn't need the path but you need the sheet name

If A6 contained

[051115]Sheet1!$A$1:$J$10

then this formula should work

=VLOOKUP(A8,INDIRECT(ranged),2,0)

If you want to reference a closed workbook then you might need INDIRECT.EXT which is part of the MOREFUNC add-in

 

[Corbana]

is their any way to do this using vba maybe? even if it was to import the file into another sheet in the spreadsheet and then vloopkup on that information that would do.