Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link formula (Excel 2000)

    Currently my formula reads:

    ='sharesjohn[A115924.xls]M01'!$B$21

    I would like to change it to : ='shares" & B3 & "[" & c3 & ".xls]" & D3 &"'!$B$21 "

    where b3 = john, c3 = A115924, d3 = M01

    Thanks for any help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link formula (Excel 2000)

    You would need the INDIRECT function for this:
    <code>=INDIRECT("'shares" & B3 & "[" & c3 & ".xls]" & D3 &"'!$B$21")</code>
    but INDIRECT only works if the workbook indirectly referred to is open. If the workbook is closed, it returns #REF.

    If you need a valid result if the other workbook is closed, download Laurent Longre's free add-in Morefunc.dll here. It contains a more powerful version of INDIRECT that works for closed workbooks.

Posting Permissions

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