Indirect or substitute?

[nmorson]

Okay, I'm trying to get the INDIRECT function to look up a UNC address
stored in another cell so based upon this formula:

=VLOOKUP("my data",INDIRECT("'C:\temp\[spreadsheet.xls]sheet1'!$C$12:$I
$17"),1,FALSE)

....you would think that this would work:

=VLOOKUP("my data",INDIRECT(A1),1,FALSE)

....where cell A1 contains the UNC pathway C:\temp\
[spreadsheet.xls]sheet1'!$C$12:$I$17

but it doesn't, hence my post. So I tried changing the formula to
this:

=VLOOKUP("my data",INDIRECT("'C:\blank\[spreadsheet.xls]sheet1'!$C
$12:$I$17"),1,FALSE)

....and using this...

=SUBSTITUTE(E11, "blank", G12)

(E11 being the cell where the VLOOKUP is, G12 being the data to
complete the UNC address)

I've searched many forums and learned a lot, coming close to solving
this on a couple of occasions - but still no cigar. A techy at work
said the INDIRECT function cannot parse a text string which is why
it's not working. So how do I make it otherwise??? Thanks for looking

 

[Pete_UK]

You can only use INDIRECT on a file which is open at the same time (in
which case you would not need the C:\temp\ part in A1).

If you can't arrange for the file to be open at the same time, then
maybe you can try INDIRECT.EXT, which is part of Laurent Longre's
MOREFUNC addin, available here:

http://xcell05.free.fr/english/

Hope this helps.

Pete

 

[Ron Rosenfeld]

Okay, I'm trying to get the INDIRECT function to look up a UNC address
stored in another cell so based upon this formula:

=VLOOKUP("my data",INDIRECT("'C:\temp\[spreadsheet.xls]sheet1'!$C$12:$I
$17"),1,FALSE)

...you would think that this would work:

=VLOOKUP("my data",INDIRECT(A1),1,FALSE)

...where cell A1 contains the UNC pathway C:\temp\
[spreadsheet.xls]sheet1'!$C$12:$I$17

but it doesn't, hence my post. So I tried changing the formula to
this:

=VLOOKUP("my data",INDIRECT("'C:\blank\[spreadsheet.xls]sheet1'!$C
$12:$I$17"),1,FALSE)

...and using this...

=SUBSTITUTE(E11, "blank", G12)

(E11 being the cell where the VLOOKUP is, G12 being the data to
complete the UNC address)

I've searched many forums and learned a lot, coming close to solving
this on a couple of occasions - but still no cigar. A techy at work
said the INDIRECT function cannot parse a text string which is why
it's not working. So how do I make it otherwise??? Thanks for looking

Is the workbook C:\temp\... open? Indirect cannot access closed workbooks.
--ron

 

[nmorson]

Thanks - yes the workbook is open and I am aware that INDIRECT cannot
access a closed workbook. I intend to use Laurent Longre's solution
but first must get the formula to work. Laurent's solution as far as
I'm aware is one that allows INDIRECT to access a closed workbook. I
must still use a formula that allows me to specify where that workbook
is. And the workbook will not always be in one place. I have around
250 students in a high school who will have identical copies of the
spreadsheet, stored under folders with their usernames.

All I need is a spreadsheet with their usernames stored in it, and
tell INDIRECT to call their username from that sheet. This is where
the problem begins

 

[Ron Rosenfeld]

Thanks - yes the workbook is open and I am aware that INDIRECT cannot
access a closed workbook. I intend to use Laurent Longre's solution
but first must get the formula to work. Laurent's solution as far as
I'm aware is one that allows INDIRECT to access a closed workbook. I
must still use a formula that allows me to specify where that workbook
is. And the workbook will not always be in one place. I have around
250 students in a high school who will have identical copies of the
spreadsheet, stored under folders with their usernames.

All I need is a spreadsheet with their usernames stored in it, and
tell INDIRECT to call their username from that sheet. This is where
the problem begins

There may be something wrong with your format, then, as the concept works and I
use it (along with INDIRECT.EXT) in several of my workbooks.

...where cell A1 contains the UNC pathway C:\temp\
[spreadsheet.xls]sheet1'!$C$12:$I$17

If that is truly the case, then you are missing the single quote prior to the
C:\. Be aware that you may have to precede the C: by two (2) single quotes in
order to have it appear. (A single, single quote gets interpreted as the text
qualifier).

Use the formula evaluator tool to see exactly what is being passed to the
INDIRECT function.
--ron

 

[nmorson]

There may be something wrong with your format, then, as the concept works and I

use it (along with INDIRECT.EXT) in several of my workbooks.

If that is truly the case, then you are missing the single quote prior to the
C:\.  Be aware that you may have to precede the C: by two (2) single quotes in
order to have it appear.  (A single, single quote gets interpreted as the text
qualifier).

Use the formula evaluator tool to see exactly what is being passed to the
INDIRECT function.
--ron

cheers Ron - I have used the formula evaluator tool only for it to
tell me #ref is being passed! I've downloaded and installed the
INDIRECT.EXT function and tried it as so:

=VLOOKUP("my data",INDIRECT.EXT(A1),1,FALSE)

...and still no joy. I just can't figure out what I'm doing wrong, but
it must be something that's staring me in the face if you say it
works. I've tried " qoutes, single qoutes, double single qoutes and no
quotes as above but I can't get the damn thing to work. Any chance you
could paste a copy of your formula here and I can try reverse
engineering it to see what's up? Thanks either way. This is turning
out to be a real bugger of a problem. Somebody told me Excel does not
make this easy - hey, they weren't joking!

 

[nmorson]

cheers Ron - I have used the formula evaluator tool only for it to
tell me #ref is being passed! I've downloaded and installed the
INDIRECT.EXT function and tried it as so:

=VLOOKUP("my data",INDIRECT.EXT(A1),1,FALSE)

...and still no joy. I just can't figure out what I'm doing wrong, but
it must be something that's staring me in the face if you say it
works. I've tried " qoutes, single qoutes, double single qoutes and no
quotes as above but I can't get the damn thing to work. Any chance you
could paste a copy of your formula here and I can try reverse
engineering it to see what's up? Thanks either way. This is turning
out to be a real bugger of a problem. Somebody told me Excel does not
make this easy - hey, they weren't joking!

Whoah! Wait a moment. It works! I must be overtired (or stupid).
Single parenthesis. Works great. Many thanks for persevering with
me

 

[Ron Rosenfeld]

Whoah! Wait a moment. It works! I must be overtired (or stupid).
Single parenthesis. Works great. Many thanks for persevering with
me

I think it was you who did the persevering. But I'm glad you got it to work.
--ron