Vlookup from a closed workbook?

Lt. Krul · May 22nd, 2002, 11:31 AM

I'd like to do a vlookup from a closed workbook. can this be done. I don't want my users to have to open the other workbook everytime they use the vlookup function.

Aladin Akyurek · May 22nd, 2002, 11:39 AM

Quote:

On 2002-05-22 10:31, Lt. Krul wrote:
I'd like to do a vlookup from a closed workbook. can this be done. I don't want my users to have to open the other workbook everytime they use the vlookup function.

Yes, as long as you don't throw in the formula something like INDIRECT.

Lt. Krul · May 22nd, 2002, 12:02 PM

OK - heres what I have:
workbook A contains cells that have the Vlookup function applied
the function is =VLOOKUP(A16,ProjectName,2,0)
where: A16 is the lookup value
ProjectName is the named Range
2 is the Column I'm interested in
0 is the Range lookup value

ProjectName is defined as: ='[workbook b]sheet1'!$A$2:$E$8000

So far the vlookup only works when Workbook B is open. Any Ideas...(I'm not so good at this programming stuff yet)

Lt. Krul · May 22nd, 2002, 12:13 PM

Oops...let me clarify something from my past post...

The Cell A14 is attempting to Apply data validation from data in that closed workbook B. The Vlookup reads the value from A14 and attempts to return a value from column C in that same workbook B.

Does that confuse the issue

I suppose my question now is: can I perform Data validation from a closed workbook?

Thanks.

Aladin Akyurek · May 22nd, 2002, 12:16 PM

Quote:

On 2002-05-22 11:02, Lt. Krul wrote:
OK - heres what I have:
workbook A contains cells that have the Vlookup function applied
the function is =VLOOKUP(A16,ProjectName,2,0)
where: A16 is the lookup value
ProjectName is the named Range
2 is the Column I'm interested in
0 is the Range lookup value

ProjectName is defined as: ='[workbook b]sheet1'!$A$2:$E$8000

So far the vlookup only works when Workbook B is open. Any Ideas...(I'm not so good at this programming stuff yet)

Go to the workbook b, select A2:E8000, go to the Name Box on the Formula Bar, type PROJECTS (a better name, no?), hit enter.

=VLOOKUP(A16,ProjectName,2,0)

becomes something like

=VLOOKUP(A16,'C:.........workbook b.xls'!PROJECTS,2,0)

Dave Patton · May 22nd, 2002, 12:26 PM

try creating the formula again with reference to the named formula in the other ss.

- name the formula in the other ss or reference the relevant range.

=VLOOKUP(A16,Workbookb.xls!ProjectName,2,0)

This formula shows the full path when the other SS is closed.

=VLOOKUP(A16,'E:data02Tech02Workbookb.xls'!ProjectName,2,0)

The path will be different on your system.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 22nd, 2002, 11:31 AM	#1
Lt. Krul New Member Join Date: May 2002 Posts: 31	I'd like to do a vlookup from a closed workbook. can this be done. I don't want my users to have to open the other workbook everytime they use the vlookup function.

May 22nd, 2002, 12:02 PM	#3
Lt. Krul New Member Join Date: May 2002 Posts: 31	OK - heres what I have: workbook A contains cells that have the Vlookup function applied the function is =VLOOKUP(A16,ProjectName,2,0) where: A16 is the lookup value ProjectName is the named Range 2 is the Column I'm interested in 0 is the Range lookup value ProjectName is defined as: ='[workbook b]sheet1'!$A$2:$E$8000 So far the vlookup only works when Workbook B is open. Any Ideas...(I'm not so good at this programming stuff yet)

May 22nd, 2002, 12:13 PM	#4
Lt. Krul New Member Join Date: May 2002 Posts: 31	Oops...let me clarify something from my past post... The Cell A14 is attempting to Apply data validation from data in that closed workbook B. The Vlookup reads the value from A14 and attempts to return a value from column C in that same workbook B. Does that confuse the issue I suppose my question now is: can I perform Data validation from a closed workbook? Thanks.

May 22nd, 2002, 12:26 PM	#6
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	try creating the formula again with reference to the named formula in the other ss. - name the formula in the other ss or reference the relevant range. =VLOOKUP(A16,Workbookb.xls!ProjectName,2,0) This formula shows the full path when the other SS is closed. =VLOOKUP(A16,'E:data02Tech02Workbookb.xls'!ProjectName,2,0) The path will be different on your system.