This shouldn't be this difficult I feel!

I am trying to do a basic Vlookup, referencing an array and returning a
specific value. The trick is the location of the array is in a
different excel workbook, which is defined in a seperate cell.

In cell A1 lies the path of the workbook as well as the sheet name and
array info

A1='\\server\sharedfolder\[LCV.xls]QTR'!H:I

In cell A2 is the lookup, which searches for the value "25th" in the
range defined in cell A1, and returns the value in the second column.

A2=VLOOKUP("25th",A1,2,FALSE)

The problem is, this doesn't work, it won't accept what I have in A1 as
a range, I have tried messing with the single quotes and a few other
format type things in A1, but cannot get this to work.

This does work

=VLOOKUP("25th",'\\server\sharedfolder\[LCV.xls]QTR'!H:I,2,FALSE)

so I know my array is correct.

Please HELP!

Ed


---
Message posted from http://www.ExcelForum.com/

Hi
unfortunately neither INDIRECT nor INDIRECT.EXT (from the add-in
morefunc.xll) will work in this case as
- the other workbook is closed AND
- you need an array returned from the close workbook

Have a look at http://tinyurl.com/2c62u
for further alternatives. Note: They will probably be quite slow so you
may consider the following workaround:
- 'mirror' the cells with INDIRECT.EXT (also described in the link from
above) on a separate sheet in your workbook
- use VLOOKUP on this mirrored sheet
Though this could also be quite slow

Besides these formula solutions you may consider using VBA (though this
also has to access the closed workbook)


--
Regards
Frank Kabel
Frankfurt, Germany

This shouldn't be this difficult I feel!

I am trying to do a basic Vlookup, referencing an array and returning
a specific value. The trick is the location of the array is in a
different excel workbook, which is defined in a seperate cell.

In cell A1 lies the path of the workbook as well as the sheet name
and
array info

A1='\\server\sharedfolder\[LCV.xls]QTR'!H:I

In cell A2 is the lookup, which searches for the value "25th" in the
range defined in cell A1, and returns the value in the second column.

A2=VLOOKUP("25th",A1,2,FALSE)

The problem is, this doesn't work, it won't accept what I have in A1
as a range, I have tried messing with the single quotes and a few
other format type things in A1, but cannot get this to work.

This does work

=VLOOKUP("25th",'\\server\sharedfolder\[LCV.xls]QTR'!H:I,2,FALSE)

so I know my array is correct.

Please HELP!

Ed


---
Message posted from http://www.ExcelForum.com/

Untested, but try substituting INDIRECT(A1) for A1.

Alan Beban

edsox5 < wrote:
This shouldn't be this difficult I feel!

I am trying to do a basic Vlookup, referencing an array and returning a
specific value. The trick is the location of the array is in a
different excel workbook, which is defined in a seperate cell.

In cell A1 lies the path of the workbook as well as the sheet name and
array info

A1='\\server\sharedfolder\[LCV.xls]QTR'!H:I

In cell A2 is the lookup, which searches for the value "25th" in the
range defined in cell A1, and returns the value in the second column.

A2=VLOOKUP("25th",A1,2,FALSE)

The problem is, this doesn't work, it won't accept what I have in A1 as
a range, I have tried messing with the single quotes and a few other
format type things in A1, but cannot get this to work.

This does work

=VLOOKUP("25th",'\\server\sharedfolder\[LCV.xls]QTR'!H:I,2,FALSE)

so I know my array is correct.

Please HELP!

Ed


---
Message posted from http://www.ExcelForum.com/

Did the OP indicate that the other workbook is closed?

Alan Beban

Frank Kabel wrote:
Hi
unfortunately neither INDIRECT nor INDIRECT.EXT (from the add-in
morefunc.xll) will work in this case as
- the other workbook is closed AND
- you need an array returned from the close workbook

Have a look at http://tinyurl.com/2c62u
for further alternatives. Note: They will probably be quite slow so you
may consider the following workaround:
- 'mirror' the cells with INDIRECT.EXT (also described in the link from
above) on a separate sheet in your workbook
- use VLOOKUP on this mirrored sheet
Though this could also be quite slow

Besides these formula solutions you may consider using VBA (though this
also has to access the closed workbook)


--
Regards
Frank Kabel
Frankfurt, Germany


This shouldn't be this difficult I feel!

I am trying to do a basic Vlookup, referencing an array and returning
a specific value. The trick is the location of the array is in a
different excel workbook, which is defined in a seperate cell.

In cell A1 lies the path of the workbook as well as the sheet name

and

array info

A1='\\server\sharedfolder\[LCV.xls]QTR'!H:I

In cell A2 is the lookup, which searches for the value "25th" in the
range defined in cell A1, and returns the value in the second column.

A2=VLOOKUP("25th",A1,2,FALSE)

The problem is, this doesn't work, it won't accept what I have in A1
as a range, I have tried messing with the single quotes and a few
other format type things in A1, but cannot get this to work.

This does work

=VLOOKUP("25th",'\\server\sharedfolder\[LCV.xls]QTR'!H:I,2,FALSE)

so I know my array is correct.

Please HELP!

Ed


---
Message posted from http://www.ExcelForum.com/

Hi Alan
this probably won't work as (due to the path definition) the other
workbook seems to be closed - INDIRECT will return an error

--
Regards
Frank Kabel
Frankfurt, Germany

Alan Beban wrote:
Untested, but try substituting INDIRECT(A1) for A1.

Alan Beban

edsox5 < wrote:
This shouldn't be this difficult I feel!

I am trying to do a basic Vlookup, referencing an array and
returning a specific value. The trick is the location of the array
is in a different excel workbook, which is defined in a seperate
cell.

In cell A1 lies the path of the workbook as well as the sheet name
and array info

A1='\\server\sharedfolder\[LCV.xls]QTR'!H:I

In cell A2 is the lookup, which searches for the value "25th" in the
range defined in cell A1, and returns the value in the second
column.

A2=VLOOKUP("25th",A1,2,FALSE)

The problem is, this doesn't work, it won't accept what I have in A1
as a range, I have tried messing with the single quotes and a few
other format type things in A1, but cannot get this to work.

This does work

=VLOOKUP("25th",'\\server\sharedfolder\[LCV.xls]QTR'!H:I,2,FALSE)

so I know my array is correct.

Please HELP!

Ed


---
Message posted from http://www.ExcelForum.com/

Hi Alan
just a 'good' guess on my side as he has defined the full path
information. If the other workbook is open this is not required.
But you're right, if the WB is open INDIRECT will work. I should have
stated that this was my assumption :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Alan Beban wrote:
Did the OP indicate that the other workbook is closed?

Alan Beban

Frank Kabel wrote:
Hi
unfortunately neither INDIRECT nor INDIRECT.EXT (from the add-in
morefunc.xll) will work in this case as
- the other workbook is closed AND
- you need an array returned from the close workbook

Have a look at http://tinyurl.com/2c62u
for further alternatives. Note: They will probably be quite slow so
you may consider the following workaround:
- 'mirror' the cells with INDIRECT.EXT (also described in the link
from above) on a separate sheet in your workbook
- use VLOOKUP on this mirrored sheet
Though this could also be quite slow

Besides these formula solutions you may consider using VBA (though
this also has to access the closed workbook)


--
Regards
Frank Kabel
Frankfurt, Germany


This shouldn't be this difficult I feel!

I am trying to do a basic Vlookup, referencing an array and
returning a specific value. The trick is the location of the array
is in a different excel workbook, which is defined in a seperate
cell.

In cell A1 lies the path of the workbook as well as the sheet name

and

array info

A1='\\server\sharedfolder\[LCV.xls]QTR'!H:I

In cell A2 is the lookup, which searches for the value "25th" in
the
range defined in cell A1, and returns the value in the second
column.

A2=VLOOKUP("25th",A1,2,FALSE)

The problem is, this doesn't work, it won't accept what I have in
A1
as a range, I have tried messing with the single quotes and a few
other format type things in A1, but cannot get this to work.

This does work

=VLOOKUP("25th",'\\server\sharedfolder\[LCV.xls]QTR'!H:I,2,FALSE)

so I know my array is correct.

Please HELP!

Ed


---
Message posted from http://www.ExcelForum.com/

Thanks for all your help everyone, downloading the Excel Add-In an
using the INDIRECT.EXT allowed me to do what I needed, it's powerfu
stuff!

Thanks for all your help!

E

--
Message posted from http://www.ExcelForum.com

"Alan Beban" wrote...
Did the OP indicate that the other workbook is closed?
...

As Frank has already pointed out, the full path isn't required when the workbook
is open. Did the OP's use of the full path raise any red flags for you?

Actually the OP stated:

"This does work

=VLOOKUP("25th",'\\server\sharedfolder\[LCV.xls]QTR'!H:I,2,FALSE)"

Taking the OP at face value (which you've been known to do in other threads),
the formula above could only be stored as such in Excel if the workbook were
closed; otherwise, Excel would have converted the formula to

=VLOOKUP("25th",[LCV.xls]QTR!H:I,2,FALSE)

Logical inference is a powerful skill, once developed. You should try it
sometime.

--
To top-post is human, to bottom-post and snip is sublime.