software

I am trying to reference data in an external workbook and would like to save
some typing. Each row has the same references, but to a different file. I
would like to just type the file name in the first cell and have each of the
following cells use that cell to get the file name and perform the function.
For instance:

='[external_file.xls]Enterprise Architecture'!$B$3

But the formula would contain some function that would get external_file.xls
from the first cell.

Hi

You can use INDIRECT function to create a link, of-course, but there is a
drawback - INDIRECT needs source file to be opened - otherwise it returns an
error.

When addressing by cell value is needed only for designing purpouses, then
my advice is to use this alternative technique:

Into some column, p.e. A, enter workbook names.
In another column, enter the formula like this:
=quot;='[quot; amp; A1 amp; quot;]Sheet1'!A1quot;
and copy it down
Select the range with formulas. Copy, and then PasteSpecialgt;Values. Leaving
the range selected, replace all quot;=quot; with quot;=quot;.
It's done!--
Arvi Laanemets
( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
quot;TJW JRquot; lt;TJW gt; wrote in message
...
gt;I am trying to reference data in an external workbook and would like to
gt;save
gt; some typing. Each row has the same references, but to a different file.
gt; I
gt; would like to just type the file name in the first cell and have each of
gt; the
gt; following cells use that cell to get the file name and perform the
gt; function.
gt; For instance:
gt;
gt; ='[external_file.xls]Enterprise Architecture'!$B$3
gt;
gt; But the formula would contain some function that would get
gt; external_file.xls
gt; from the first cell.
Try this one
www.rondebruin.nl/summary2.htm--
Regards Ron de Bruin
www.rondebruin.nlquot;TJW JRquot; lt;TJW gt; wrote in message ...
gt;I am trying to reference data in an external workbook and would like to save
gt; some typing. Each row has the same references, but to a different file. I
gt; would like to just type the file name in the first cell and have each of the
gt; following cells use that cell to get the file name and perform the function.
gt; For instance:
gt;
gt; ='[external_file.xls]Enterprise Architecture'!$B$3
gt;
gt; But the formula would contain some function that would get external_file.xls
gt; from the first cell.
Assuming your file name is in A1

the formula =MID(A1,FIND(quot;[quot;,A1) 1,FIND(quot;]quot;,A1)-1-FIND(quot;[quot;,A1))

will bring back whatever is between [ and ].

Gizmo63quot;Ron de Bruinquot; wrote:

gt; Try this one
gt; www.rondebruin.nl/summary2.htm
gt;
gt;
gt; --
gt; Regards Ron de Bruin
gt; www.rondebruin.nl
gt;
gt;
gt; quot;TJW JRquot; lt;TJW gt; wrote in message ...
gt; gt;I am trying to reference data in an external workbook and would like to save
gt; gt; some typing. Each row has the same references, but to a different file. I
gt; gt; would like to just type the file name in the first cell and have each of the
gt; gt; following cells use that cell to get the file name and perform the function.
gt; gt; For instance:
gt; gt;
gt; gt; ='[external_file.xls]Enterprise Architecture'!$B$3
gt; gt;
gt; gt; But the formula would contain some function that would get external_file.xls
gt; gt; from the first cell.
gt;
gt;
gt;