A Microsoft Excel forum. ExcelBanter

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 » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Concatenate path and file string for external reference



 
 
Thread Tools Display Modes
  #1  
Old January 13th 09, 04:58 PM posted to microsoft.public.excel.worksheet.functions
Michael[_4_]
external usenet poster
 
Posts: 27
Default 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?
Ads
  #2  
Old January 13th 09, 05:04 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,856
Default 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?


  #3  
Old January 13th 09, 05:19 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph[_2_]
external usenet poster
 
Posts: 8,651
Default 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?



  #4  
Old January 13th 09, 07:51 PM posted to microsoft.public.excel.worksheet.functions
Michael[_4_]
external usenet poster
 
Posts: 27
Default 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.
  #5  
Old January 13th 09, 08:32 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
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 -


 




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 On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Keep full file path in cell reference dan Excel Discussion (Misc queries) 0 December 6th 06 12:26 AM
Formula too long - new file path is shorter than old file path - Excel 2003 Greg J Excel Worksheet Functions 1 November 22nd 06 05:16 PM
File path of external link changed ramram49 Links and Linking in Excel 1 November 2nd 06 07:03 AM
file path: reference to other workbooks muster Excel Worksheet Functions 8 July 28th 06 09:46 PM
How do I reference external data from a file, file name found in . Clux Excel Discussion (Misc queries) 1 February 10th 05 10:52 PM


All times are GMT +1. The time now is 03:31 PM.


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