Concatenate path and file string for external reference
 

I am trying to create a linked external reference by concatinating the
info together.
I have in 3 cells:

path file sheet
H:\Dev\ [fromModel.xls] Sheet1'!$B4

I concat them to create what should be the reference
='H:\Dev\[fromModel.xls]Sheet1'!$B4

But it just returns the string. How do I get it to evaluate?

Answer: Concatenate path and file string for external reference
 

To get the concatenated string to evaluate as a reference, you can use the INDIRECT function in Excel. Here's how you can modify your formula:

1. Start by typing the INDIRECT function in a new cell where you want the reference to appear. The syntax for the INDIRECT function is:
   
   
   Formula:

   ---

   =INDIRECT(ref_text, [a1]) 


   ---

2. In the ref_text argument, you can enter your concatenated string formula. So, your formula would look like this:
   
   
   Formula:

   ---

   =INDIRECT("'"&A1&B1&C1&"'") 


   ---

   Note that we are using the ampersand (&) to concatenate the cells together, and adding single quotes around the file and sheet names to ensure that Excel recognizes them as a single reference.
   
3. Press Enter to evaluate the formula. The result should be the value in cell B4 of Sheet1 in the fromModel.xls file located at H:\Dev\.
   
   By using the INDIRECT function, you can create dynamic references that can be updated if the source data changes.

Concatenate path and file string for external reference
 

Normally with something like this you would then use the INDIRECT
function. However, this does not work on closed workbooks.

If it is important to you, you can download a free add-in called
morefunc (do a Google search for sites where this can be downloaded),
and this gives you a new function which can be used like this:

=INDIRECT.EXT(cell)

where cell contains your concatenated reference.

Hope this helps.

Pete

On Jan 13, 4:58*pm, Michael wrote:
I am trying to create a linked external reference by concatinating the
info together.
I have in 3 cells:

path * *file * *sheet
H:\Dev\ [fromModel.xls] Sheet1'!$B4

I concat them to create what should be the reference
='H:\Dev\[fromModel.xls]Sheet1'!$B4

But it just returns the string. How do I get it to evaluate?

Concatenate path and file string for external reference
 

Use the INDIRECT function.
--
David Biddulph

"Michael" wrote in message
...
I am trying to create a linked external reference by concatinating the
info together.
I have in 3 cells:

path file sheet
H:\Dev\ [fromModel.xls] Sheet1'!$B4

I concat them to create what should be the reference
='H:\Dev\[fromModel.xls]Sheet1'!$B4

But it just returns the string. How do I get it to evaluate?

Concatenate path and file string for external reference
 

On Jan 13, 12:19*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Use the INDIRECT function.
--
David Biddulph

"Michael" wrote in message

...



I am trying to create a linked external reference by concatinating the
info together.
I have in 3 cells:

path file sheet
H:\Dev\ [fromModel.xls] Sheet1'!$B4

I concat them to create what should be the reference
='H:\Dev\[fromModel.xls]Sheet1'!$B4

But it just returns the string. How do I get it to evaluate?- Hide quoted text -

- Show quoted text -

I cant seem to get indirect to work. Can someone give me an example of
how it would work with the concatenation of the path\file\sheet
\cellref.
Also is there a way to make the string with the = sign in front of it
evaluate instead of returning the concatenated string (a way around
indirect)?
thanks.

Concatenate path and file string for external reference
 

Please note my earlier comments - INDIRECT will not work with closed
workbooks. If the workbook is open, then you don't need to bother
about the full path.

Anyway, here's an example of using indirect within the same sheet:

=INDIRECT("B"&(3*ROW(A1))

In copying this down to adjacent rows, it effectively returns:

=B3
=B6
=B9

etc.

The answer to your last question is no.

Download morefunc and use INDIRECT.EXT.

Hope this helps.

Pete

On Jan 13, 7:51*pm, Michael wrote:
On Jan 13, 12:19*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:





Use the INDIRECT function.
--
David Biddulph

"Michael" wrote in message

....

I am trying to create a linked external reference by concatinating the
info together.
I have in 3 cells:

path file sheet
H:\Dev\ [fromModel.xls] Sheet1'!$B4

I concat them to create what should be the reference
='H:\Dev\[fromModel.xls]Sheet1'!$B4

But it just returns the string. How do I get it to evaluate?- Hide quoted text -

- Show quoted text -

I cant seem to get indirect to work. Can someone give me an example of
how it would work with the concatenation of the path\file\sheet
\cellref.
Also is there a way to make the string with the = sign in front of it
evaluate instead of returning the concatenated string (a way around
indirect)?
thanks.- Hide quoted text -

- Show quoted text -