Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: links to other files (Excel'97)

    You could use the formula in column A, drag it down to fill the same number of rows as in column B,

    Then copy the cells in column A and paste special values those into column C.

    Finally, select column C and do a search and replace, searching for =, replacing with =.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    links to other files (Excel'97)

    I have a list of files in Column B

    I want to return the value in cell A1 from the excel file listed in Column B

    In cell A1 I have a formula which doesn't work
    In cell C1 I have one that does work

    But I now want to be able to copy the formula to
    C2 to give the A1 value of the file in B2
    and to
    C3 to give the A1 value of the file in B3

    I currently use code ...
    ActiveCell.Formula = "='C:Folder[" & ActiveCell.Offset(0, -1).Value & ".xls]Sheet1'!A1"
    ActiveCell.Offset(1,0).Select

    using a do loop thing to populate the whole column

    Which works but as there are hundreds of files so it goes very slow.
    I would like to be able to correct the formula that I have in A1 so that I can just copy and paste it into the rest of column A

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: links to other files (Excel'97)

    Thank you Jan for your reply

    The formula in A1 is incorrect
    ='C:Folder["& B1 & ".xls]Sheet1'!A1
    it returns #REF!

    so I'm still scratching and wondering <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: links to other files (Excel'97)

    I got it now

    ... and just so soon after making the previous post <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    I changed the formula in A1 from
    ='C:Folder["& C2 & ".xls]Sheet1'!A1
    to
    ="='C:Folder["& C2 & ".xls]Sheet1'!A1"
    now it works just like you said

    thank you very much

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: links to other files (Excel'97)

    Great you found out how.
    Thanks for letting us know.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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