View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK Pete_UK is offline
external usenet poster
 
Posts: 8,856
Default 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 -