Is it possible to create a VLOOKUP in an open worksheet that collects data
from a closed worksheet?

Yes:

Say in A1 thru B3 we have:

1 ='C:\[a.xls]Sheet1'!$C$7
2 ='C:\[a b.xls]Sheet1'!$A$14
3 ='C:\Documents and Settings\jr\My Documents\[nebula.xls]Sheet1'!$B$1


=VLOOKUP(3,A1:B3,2) will return a value from the nebula without the nebula
being open.
--
Gary''s Student - gsnu200777


"Jeremy" wrote:

Is it possible to create a VLOOKUP in an open worksheet that collects data
from a closed worksheet?

Thanks, I think this will work. One followup though, I am looking to create
an input box that will create the file reference on its own. One with like a
browes option. Is that possible to code?

"Gary''s Student" wrote:

Yes:

Say in A1 thru B3 we have:

1 ='C:\[a.xls]Sheet1'!$C$7
2 ='C:\[a b.xls]Sheet1'!$A$14
3 ='C:\Documents and Settings\jr\My Documents\[nebula.xls]Sheet1'!$B$1


=VLOOKUP(3,A1:B3,2) will return a value from the nebula without the nebula
being open.
--
Gary''s Student - gsnu200777


"Jeremy" wrote:

Is it possible to create a VLOOKUP in an open worksheet that collects data
from a closed worksheet?

I am sure it is possible. It would need a macro to display and apply a
userform. This is something I don't know much about. IF you want to
investgate this, place a new post in:

http://www.microsoft.com/office/comm...&lang=en&cr=US
--
Gary''s Student - gsnu200777


"Jeremy" wrote:

Thanks, I think this will work. One followup though, I am looking to create
an input box that will create the file reference on its own. One with like a
browes option. Is that possible to code?

"Gary''s Student" wrote:

Yes:

Say in A1 thru B3 we have:

1 ='C:\[a.xls]Sheet1'!$C$7
2 ='C:\[a b.xls]Sheet1'!$A$14
3 ='C:\Documents and Settings\jr\My Documents\[nebula.xls]Sheet1'!$B$1


=VLOOKUP(3,A1:B3,2) will return a value from the nebula without the nebula
being open.
--
Gary''s Student - gsnu200777


"Jeremy" wrote:

Is it possible to create a VLOOKUP in an open worksheet that collects data
from a closed worksheet?

OK, I'm still having issues. I got how to have the file name and location put
in the cell. This is what I have:

A1 = \\uswifs01\users07b\ko23693\My Documents\Process Improvements\RO99\R099
03-24.xls

I need the VLOOKUP to look like the below:

C6= VLOOKUP(B:B,[WORKSHEET IN A1]B:C,2,false)
D6= VLOOKUP(B:B,,[WORKSHEET IN A1]B:D,3,false)

What I am hvaing trouble with is copyin the value in A1 (FILE NAME AND
LOCATION) into the formula.



"Gary''s Student" wrote:

I am sure it is possible. It would need a macro to display and apply a
userform. This is something I don't know much about. IF you want to
investgate this, place a new post in:

http://www.microsoft.com/office/comm...&lang=en&cr=US
--
Gary''s Student - gsnu200777


"Jeremy" wrote:

Thanks, I think this will work. One followup though, I am looking to create
an input box that will create the file reference on its own. One with like a
browes option. Is that possible to code?

"Gary''s Student" wrote:

Yes:

Say in A1 thru B3 we have:

1 ='C:\[a.xls]Sheet1'!$C$7
2 ='C:\[a b.xls]Sheet1'!$A$14
3 ='C:\Documents and Settings\jr\My Documents\[nebula.xls]Sheet1'!$B$1


=VLOOKUP(3,A1:B3,2) will return a value from the nebula without the nebula
being open.
--
Gary''s Student - gsnu200777


"Jeremy" wrote:

Is it possible to create a VLOOKUP in an open worksheet that collects data
from a closed worksheet?

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Jeremy wrote:

OK, I'm still having issues. I got how to have the file name and location put
in the cell. This is what I have:

A1 = \\uswifs01\users07b\ko23693\My Documents\Process Improvements\RO99\R099
03-24.xls

I need the VLOOKUP to look like the below:

C6= VLOOKUP(B:B,[WORKSHEET IN A1]B:C,2,false)
D6= VLOOKUP(B:B,,[WORKSHEET IN A1]B:D,3,false)

What I am hvaing trouble with is copyin the value in A1 (FILE NAME AND
LOCATION) into the formula.

"Gary''s Student" wrote:

I am sure it is possible. It would need a macro to display and apply a
userform. This is something I don't know much about. IF you want to
investgate this, place a new post in:

http://www.microsoft.com/office/comm...&lang=en&cr=US
--
Gary''s Student - gsnu200777


"Jeremy" wrote:

Thanks, I think this will work. One followup though, I am looking to create
an input box that will create the file reference on its own. One with like a
browes option. Is that possible to code?

"Gary''s Student" wrote:

Yes:

Say in A1 thru B3 we have:

1 ='C:\[a.xls]Sheet1'!$C$7
2 ='C:\[a b.xls]Sheet1'!$A$14
3 ='C:\Documents and Settings\jr\My Documents\[nebula.xls]Sheet1'!$B$1


=VLOOKUP(3,A1:B3,2) will return a value from the nebula without the nebula
being open.
--
Gary''s Student - gsnu200777


"Jeremy" wrote:

Is it possible to create a VLOOKUP in an open worksheet that collects data
from a closed worksheet?

--

Dave Peterson