Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Linking to Worksheets named in cells

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a sheet listing workbooks in column A, what i want to do is extract data from the same cell in each workbook
    ie
    =[Book1.xls]Sheet1!$D$5
    =[Book2.xls]Sheet1!$D$5

    Is there a way to format the Sheet name cells so they can be added to that formula, an extract function or does it have to be done by VB ?

    nb: there are 2000+ Worksbooks that i need to extract the data from an identical cell (D5)from

    Cheers


  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Howdy,

    The formulation approach is use of the INDIRECT function. Since you already have the file and cell reference in A you would use : =INDIRECT(A2) for example - although you may need the full path of the file listed.

    Sadly there's a nasty drawback to INDIRECT with filenames: the files need to be OPEN for the INDIRECT to recognize the link - might be a serious problem if your dealing with a large number of files.

    Anyone have a VBA solution?

    Adam

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers Adam,
    I'm trying to avoid opening all the workbooks though - once i've done that, i may as well cut n paste the value from D5 into the master file

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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