The basic description of the problem is that I have data scattered in a bunch
of date-named files, and I am trying to assemble them back into the same
table using VLOOKUP function, where I have to change the workbook source
names - and I am getting an error, indicating that my reference is a text
string (which it is), not the proper reference.

Example:

What it should be:
A1= '11-20 (date)
B1= 'C:\Folder\11-20 sales.xls'!data$A$1:$D$35 (reference text as a result
of a formula)

C1=vlookup(A1,indirect(B1),false,2) - ref error

When I tried to concantinate the reference in the vlookup formula itself I
got value error from inability to convert text into reference link, like this:
vlookup(A1,"'C:\Folder\11-20 sales.xls'!data$A$1:$D$35",false,2)

Appreciate your help,

PS I am not good with VB.

The other workbook needs to be open or else INDIRECT will not work

You can download morefunc (which is an Excel add-in) from here, it has a
function called INDIRECT.EXT which will work on closed workbooks

http://xcell05.free.fr/



--
Regards,

Peo Sjoblom



"McGonnagal" wrote in message
...
The basic description of the problem is that I have data scattered in a
bunch
of date-named files, and I am trying to assemble them back into the same
table using VLOOKUP function, where I have to change the workbook source
names - and I am getting an error, indicating that my reference is a text
string (which it is), not the proper reference.

Example:

What it should be:
A1= '11-20 (date)
B1= 'C:\Folder\11-20 sales.xls'!data$A$1:$D$35 (reference text as a result
of a formula)

C1=vlookup(A1,indirect(B1),false,2) - ref error

When I tried to concantinate the reference in the vlookup formula itself I
got value error from inability to convert text into reference link, like
this:
vlookup(A1,"'C:\Folder\11-20 sales.xls'!data$A$1:$D$35",false,2)

Appreciate your help,

PS I am not good with VB.

Workbook was open :-(

"Peo Sjoblom" wrote:

The other workbook needs to be open or else INDIRECT will not work

You can download morefunc (which is an Excel add-in) from here, it has a
function called INDIRECT.EXT which will work on closed workbooks

http://xcell05.free.fr/



--
Regards,

Peo Sjoblom



"McGonnagal" wrote in message
...
The basic description of the problem is that I have data scattered in a
bunch
of date-named files, and I am trying to assemble them back into the same
table using VLOOKUP function, where I have to change the workbook source
names - and I am getting an error, indicating that my reference is a text
string (which it is), not the proper reference.

Example:

What it should be:
A1= '11-20 (date)
B1= 'C:\Folder\11-20 sales.xls'!data$A$1:$D$35 (reference text as a result
of a formula)

C1=vlookup(A1,indirect(B1),false,2) - ref error

When I tried to concantinate the reference in the vlookup formula itself I
got value error from inability to convert text into reference link, like
this:
vlookup(A1,"'C:\Folder\11-20 sales.xls'!data$A$1:$D$35",false,2)

Appreciate your help,

PS I am not good with VB.

Then there is no need for a path, try this formula

=VLOOKUP(A1,INDIRECT("'"&A2),2,0)


and change the value in A2 to


[11-20 sales.xls]data'!$A$1:$D$35


no leading apostrophe

your vlookup had the wrong syntax as well


--
Regards,

Peo Sjoblom


"McGonnagal" wrote in message
...
Workbook was open :-(

"Peo Sjoblom" wrote:

The other workbook needs to be open or else INDIRECT will not work

You can download morefunc (which is an Excel add-in) from here, it has a
function called INDIRECT.EXT which will work on closed workbooks

http://xcell05.free.fr/



--
Regards,

Peo Sjoblom



"McGonnagal" wrote in message
...
The basic description of the problem is that I have data scattered in a
bunch
of date-named files, and I am trying to assemble them back into the
same
table using VLOOKUP function, where I have to change the workbook
source
names - and I am getting an error, indicating that my reference is a
text
string (which it is), not the proper reference.

Example:

What it should be:
A1= '11-20 (date)
B1= 'C:\Folder\11-20 sales.xls'!data$A$1:$D$35 (reference text as a
result
of a formula)

C1=vlookup(A1,indirect(B1),false,2) - ref error

When I tried to concantinate the reference in the vlookup formula
itself I
got value error from inability to convert text into reference link,
like
this:
vlookup(A1,"'C:\Folder\11-20 sales.xls'!data$A$1:$D$35",false,2)

Appreciate your help,

PS I am not good with VB.

OK, I found it - Excel ate up the first '....

"Peo Sjoblom" wrote:

The other workbook needs to be open or else INDIRECT will not work

You can download morefunc (which is an Excel add-in) from here, it has a
function called INDIRECT.EXT which will work on closed workbooks

http://xcell05.free.fr/



--
Regards,

Peo Sjoblom



"McGonnagal" wrote in message
...
The basic description of the problem is that I have data scattered in a
bunch
of date-named files, and I am trying to assemble them back into the same
table using VLOOKUP function, where I have to change the workbook source
names - and I am getting an error, indicating that my reference is a text
string (which it is), not the proper reference.

Example:

What it should be:
A1= '11-20 (date)
B1= 'C:\Folder\11-20 sales.xls'!data$A$1:$D$35 (reference text as a result
of a formula)

C1=vlookup(A1,indirect(B1),false,2) - ref error

When I tried to concantinate the reference in the vlookup formula itself I
got value error from inability to convert text into reference link, like
this:
vlookup(A1,"'C:\Folder\11-20 sales.xls'!data$A$1:$D$35",false,2)

Appreciate your help,

PS I am not good with VB.

correction: I did use correct spelling for the link:
'C:\Folder\[11-20 sales.xls]data'!$A$1:$D$35

Formula using Indirect() works in the same file (different worksheet), but
not outside of the workbook... grh....

"McGonnagal" wrote:

The basic description of the problem is that I have data scattered in a bunch
of date-named files, and I am trying to assemble them back into the same
table using VLOOKUP function, where I have to change the workbook source
names - and I am getting an error, indicating that my reference is a text
string (which it is), not the proper reference.

Example:

What it should be:
A1= '11-20 (date)
B1= 'C:\Folder\11-20 sales.xls'!data$A$1:$D$35 (reference text as a result
of a formula)

C1=vlookup(A1,indirect(B1),false,2) - ref error

When I tried to concantinate the reference in the vlookup formula itself I
got value error from inability to convert text into reference link, like this:
vlookup(A1,"'C:\Folder\11-20 sales.xls'!data$A$1:$D$35",false,2)

Appreciate your help,

PS I am not good with VB.