Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi gang,
    I am trying to use a defined name to specify the path and filename of a workbook I need to hook into.
    Instead of having ='G:\MYDOCS\[MyFile.XLS]Sheet1'!C3 I would have just =DefinedName!C3 or something like that.
    I tried several combination and keep getting an error message when I try to define the name.
    What am I missing?
    This would simplify my formulas so much since you can imagine I did not put the entire path in my example.
    tks.
    Johanne Champagne
    Montreal (Quebec) CANADA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't have a defined name refer to a sheet. You can have it refer to a range on a sheet in another workbook, i.e. you could create a name Test that refers to

    ='G:\MYDOCS\[MyFile.XLS]Sheet1'!C3

    and then use it in a cell formula as

    =Test

    Another option is to enter the path as a text string in a cell. For example, you could enter

    ''G:\MYDOCS\[MyFile.XLS]Sheet1'

    in cell A1 (note the two single quotes at the beginning), and a formula

    =INDIRECT(A1&"!C3")

    in another cell. This formula will return the value of cell 'G:\MYDOCS\[MyFile.XLS]Sheet1'!C3 as long as MyFile.XLS is open - unfortunately, INDIRECT does not work with external references to a closed workbook.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Unfortunately the other workbook will probably be closed.
    I will keep the filepath as it is.
    Tks.
    Johanne Champagne
    Montreal (Quebec) CANADA

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Laurent Longre has written a free add-in Morefunc for Excel that provides a function INDIRECT.EXT that works like INDIRECT but allows the other workbook to be closed. All users would need to install this add-in.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Laurent Longre has written a free add-in Morefunc for Excel that provides a function INDIRECT.EXT that works like INDIRECT but allows the other workbook to be closed. All users would need to install this add-in.
    The link associated to download Add-in has been broken & occurring following error :
    <BR style="mso-special-character: line-break"><BR style="mso-special-character: line-break">


    Warning: main(./common/init.php) [function.main]: failed to open stream: No such file or directory in /mnt/108/sdc/2/4/xcell05/fclicksql/fclick.php on line 16

    Fatal error: main() [
    function.require]: Failed opening required './common/init.php' (include_path='/mnt/108/sdc/2/4/xcell05/include:.:/usr/php4/lib/php') in /mnt/108/sdc/2/4/xcell05/fclicksql/fclick.php on line 16

    Regards
    Prasad

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hereis a alternative site to download the same.
    Regards
    Prasad

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The link that I posted works for me...

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    The link that I posted works for me...
    Strange, It is still not working for me, neither with Firefox 3.5.5 nor with IE 8. I have checked with some other links & all are working properly. Is something wrong with installation of browser at my end??
    Regards
    Prasad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Prasad View Post
    Strange, It is still not working for me, neither with Firefox 3.5.5 nor with IE 8. I have checked with some other links & all are working properly. Is something wrong with installation of browser at my end??
    I have no idea why it doesn't work for you, sorry.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •