Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jeff Lowenstein
 
Posts: n/a
Default Vlookup using variable path name for range value

I have followed the examples here, but I still can't get excel to do what I
want, and I believe I'm having a syntax issue.

In my workbook, I'm trying to refer to a workbook located on a netwrok drive:
The network path and file name is:
P:\My Documents\Alpha_Numeric.xls

in cell A11 is the text: P:\My Documents\[Alpha_Numeric.xls]Sheet1

What I want to do is first a simple indirect using this string on a single
cell.

i.e. =INDIRECT("'"&A11&"'!$A:$2")

This is not working. I am getting a #REF error. I am getting some same
error trying to use indirect on a file on my desktop.

cell a3 = C:\Documents and Settings\#jlowens\Desktop\[Alpha_Numeric.xls]Sheet1
=INDIRECT("'"&A3&"'!$a:$2")

Can someone please explain what I am doing wrong.

Thank you
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default Vlookup using variable path name for range value

Indirect needs the other workbook to be open. Laurent Longre has a utility
called Morefunc that has a function called INDIREC.EXT that will work and
Harlan Grove wrote a UDF called Pull

http://www.download.com/Morefunc/300...-10423160.html

ftp://members.aol.com/hrlngrv/ (look for pull.zip)

--
Regards,

Peo Sjoblom

Portland, Oregon




"Jeff Lowenstein" wrote in
message ...
I have followed the examples here, but I still can't get excel to do what I
want, and I believe I'm having a syntax issue.

In my workbook, I'm trying to refer to a workbook located on a netwrok
drive:
The network path and file name is:
P:\My Documents\Alpha_Numeric.xls

in cell A11 is the text: P:\My Documents\[Alpha_Numeric.xls]Sheet1

What I want to do is first a simple indirect using this string on a single
cell.

i.e. =INDIRECT("'"&A11&"'!$A:$2")

This is not working. I am getting a #REF error. I am getting some same
error trying to use indirect on a file on my desktop.

cell a3 = C:\Documents and
Settings\#jlowens\Desktop\[Alpha_Numeric.xls]Sheet1
=INDIRECT("'"&A3&"'!$a:$2")

Can someone please explain what I am doing wrong.

Thank you


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Variable col_index_num in vlookup Hugh Murfitt Excel Discussion (Misc queries) 1 February 15th 06 01:17 PM
Creating cell references with variable values to use in Vlookup jackebro Excel Discussion (Misc queries) 2 December 14th 05 03:46 PM
Problems using a variable as a Table_Array in VLOOKUP Jeff Lowenstein Excel Worksheet Functions 1 July 16th 05 02:12 AM
Do I need a sumif or sum of a vlookup formula? PeterB Excel Worksheet Functions 0 June 1st 05 12:23 PM
make a vlookup using a variable path Alex St-Pierre Excel Worksheet Functions 1 March 3rd 05 12:54 AM


All times are GMT +1. The time now is 01:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"