I am trying to refer to a cell to open an external link

[P Grieshop]

Here is my problem. I want to access external file(s) based on a name in a cell

A1 Example1
A2 =C:\Test\"A1".xls!$a$3

As the above formula stands I want it to open a file with the following
C:\Test\Example1.xls$a$3

If A1 where changed to Example2 then the following would open
C:\Test\Example2.xls$a$3

In short I want the path and file name changeable based on a cell reference.

Thanks in advance for any help you can give me.

 

[Dave Peterson]

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.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

 

[P Grieshop]

If I use the addin feature, will I be able to give the spread sheet to
another person and it work the same as if it were still on my machine?

 

[P Grieshop]

If I use the addin will I be able to give the spread sheet to another person
and have it perform the same on their machine?

 

[Dave Peterson]

Nope.

Each user who needs the formula will need the addin.

But you convert your formulas to values, then share the workbook.

 

[P Grieshop]

Now that I have the add in and figured it out. I can not get indirect.ext to
work with VLOOKUP. Can you help? here is my formula

A1 = testbook
=VLOOKUP(4,INDIRECT.EXT("'D:\[" & A1 & ".xlsx]Sheet1'!A:A",93,FALSE)

I have try all different combinations and can not get it to work. I can get
it to access and retrieve anything in the file, just nothing in an array.

What I need to do is find a value in a column and retrieve a value from a
different column of the same row.

 

[Dave Peterson]

If you're trying to return column 93 of a range, then that range has to be at
least 93 columns wide. Your range is just A:A--a single column.

So you need at least A:CO.

The next thing is that you may find that that range is too large (too many
rows). If you get an error back from excel (cannot complete action with
available resources (or something like that), then try using a smaller number of
rows.

You may find that using =index(match()) would take less resources, too.

=index(...sheet1'!co1:co99,match(4,...sheet1'!a1:a99,0))

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

Now that I have the add in and figured it out. I can not get indirect.ext to
work with VLOOKUP. Can you help? here is my formula

A1 = testbook
=VLOOKUP(4,INDIRECT.EXT("'D:\[" & A1 & ".xlsx]Sheet1'!A:A",93,FALSE)

I have try all different combinations and can not get it to work. I can get
it to access and retrieve anything in the file, just nothing in an array.

What I need to do is find a value in a column and retrieve a value from a
different column of the same row.

 

[P Grieshop]

Thanks for all you help Dave. I think I have figure out what I needed. A
combination of Indirect.ext Index and Match seem to fit the bill.
Great job, two thumbs up!

If you're trying to return column 93 of a range, then that range has to be at
least 93 columns wide. Your range is just A:A--a single column.

So you need at least A:CO.

The next thing is that you may find that that range is too large (too many
rows). If you get an error back from excel (cannot complete action with
available resources (or something like that), then try using a smaller number of
rows.

You may find that using =index(match()) would take less resources, too.

=index(...sheet1'!co1:co99,match(4,...sheet1'!a1:a99,0))

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

Now that I have the add in and figured it out. I can not get indirect.ext to
work with VLOOKUP. Can you help? here is my formula

A1 = testbook
=VLOOKUP(4,INDIRECT.EXT("'D:\[" & A1 & ".xlsx]Sheet1'!A:A",93,FALSE)

I have try all different combinations and can not get it to work. I can get
it to access and retrieve anything in the file, just nothing in an array.

What I need to do is find a value in a column and retrieve a value from a
different column of the same row.

Nope.

Each user who needs the formula will need the addin.

But you convert your formulas to values, then share the workbook.

P Grieshop wrote:

If I use the addin will I be able to give the spread sheet to another person
and have it perform the same on their machine?

:

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.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

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

P Grieshop wrote:

Here is my problem. I want to access external file(s) based on a name in a cell

A1 Example1
A2 =C:\Test\"A1".xls!$a$3

As the above formula stands I want it to open a file with the following
C:\Test\Example1.xls$a$3

If A1 where changed to Example2 then the following would open
C:\Test\Example2.xls$a$3

In short I want the path and file name changeable based on a cell reference.

Thanks in advance for any help you can give me.