A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to Build an External Link Using Data from Cells



 
 
Thread Tools Display Modes
  #1  
Old January 19th, 2004, 11:32 PM
external usenet poster
 
Posts: n/a
Default How to Build an External Link Using Data from Cells

I want to import data in a cell from an external
spreadsheet located at 'C:\Data\[MyXLS.xls]MySheet!$H$5.

I want to build it using cells from the current worksheet
such as:
A1 = C:\Data
A2 = MyXLS.xls
A3 = MySheet

In cell A4, I want to build the file location string and
have the value displayed. For example, in A4 if I enter
something similar to "='C:\Data\[MyXLS.xls]MySheet'!$H$5"
and if H5 contained 25, then 25 would display in A4.

This works fine, but when I try to build the formula
using values from cells A1, A2 and A3 it doesn't work.

Thanks in advance for any suggestions.
  #2  
Old January 20th, 2004, 12:48 AM
Frank Kabel
external usenet poster
 
Posts: n/a
Default How to Build an External Link Using Data from Cells

Hi

you have to use the function INDIRECT. So in your example enter the
following
=INDIRECT("'" & A1 & "\[" & A2 & "]" & A3 & "'!$H$5")
BUT this will work only if the second sheet is open. INDIRECT will not
work for closed files!.

If you have to reference closed files based on other cells, you may
have a look at the free add-in Morefunc.xll at
http://longre.free.fr/english/
This includes a function INDIRECT.EXT which will overcome Excels
restrictions regarding closed files.

Another note: INDIRECT.EXT won't work in combination with functions
like VLOOKUP and closed files. But if you only want to import data,
INDIRECT.EXT should work for you

HTH
Frank


wrote:
I want to import data in a cell from an external
spreadsheet located at 'C:\Data\[MyXLS.xls]MySheet!$H$5.

I want to build it using cells from the current worksheet
such as:
A1 = C:\Data
A2 = MyXLS.xls
A3 = MySheet

In cell A4, I want to build the file location string and
have the value displayed. For example, in A4 if I enter
something similar to "='C:\Data\[MyXLS.xls]MySheet'!$H$5"
and if H5 contained 25, then 25 would display in A4.

This works fine, but when I try to build the formula
using values from cells A1, A2 and A3 it doesn't work.

Thanks in advance for any suggestions.



  #3  
Old January 20th, 2004, 01:25 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default How to Build an External Link Using Data from Cells

"Frank Kabel" wrote...
you have to use the function INDIRECT. So in your example enter the
following
=INDIRECT("'" & A1 & "\[" & A2 & "]" & A3 & "'!$H$5")
BUT this will work only if the second sheet is open. INDIRECT will not
work for closed files!.


'sheet'? The other workbook must be open.

If you have to reference closed files based on other cells, you may
have a look at the free add-in Morefunc.xll at
http://longre.free.fr/english/
This includes a function INDIRECT.EXT which will overcome Excels
restrictions regarding closed files.

Another note: INDIRECT.EXT won't work in combination with functions
like VLOOKUP and closed files. But if you only want to import data,
INDIRECT.EXT should work for you


INDIRECT.EXT works just fine with VLOOKUP. None of the arguments to VLOOKUP need
to be ranges - any and all could be arrays (though array 4th arguments are
weird). There are few worksheet functions that require only ranges and never
arrays. Some of them are OFFSET, SUMIF, COUNTIF and RANK. There may be others,
but I'm too lazy to give an exhaustive list.

--
To top-post is human, to bottom-post and snip is sublime.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:23 AM.


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