Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: spreadsheet Links

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a workbook named Master.xls which is a master template.
    I have workbooks for each month named JanData.xls - Dec.xls

    I want to link the data from cell A1 in JanData.xls into cell A1 of
    Master.xls which is simple enough.

    The link would look like ='[JanData.xls]Sheet1'!$A$1

    But what I want to be able to do is replace the [JanData.xls] part of the link
    with a cell reference in Master.xls which would be a text entry so that the link
    could be changed to read from FebData.xls - MarData.xls etc as desired.

    Anyone any Ideas ?


  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Sheffield, UK
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, suppose the cell B2 contains the text:

    'FebData.xls

    (note the single quote so Excel recognises it as text) Then you can use this formula:

    =INDIRECT(CONCATENATE("[",B2,"]Sheet1!$A$1"))



    [ This Message was edited by: philR on 2002-05-09 02:37 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Phil

    Can' make that one work

    I'm using Excel 97 does that make any difference

    [ This Message was edited by: Peter100 on 2002-05-09 04:58 ]

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Phil
    Sorry about Sorry
    Tried it again and it worked

    Thanks very much

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Sheffield, UK
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I should have added: This will only work if you have the FebData.xls spreadsheet open at the time. For some reason, I am having difficulty getting it to do it when FebData.xls is closed, although I know it can be done. You basically have to include the pathname in the concatenate statement. Or, if there is a possibility that some of the sheets will be in different directories, include the pathname in B2 instead, and get rid of the square brackets in the concatenate statement. However, you need some single quotes in there somewhere as well, and I can't quite get it to work at the mo. I will keep trying. Let me kno if you solve it first.

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Phil
    I was just going to reply saying exactly what youv'e said "BOTH BOOKS HAVE TO BE OPEN"

    which is a bit of a snag along with it appears that they have to reside in the default Excel save directory.

    I'll keep working on it but if you come up with an answer PLEASE let me know.

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    Paul, Sydney Australia
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Could you explain the overall concept of the master file. Do you want the cell with jan.xls info replaced with the cell from feb.xls info, or do you want to keep jan info in master.xls and bring feb.xls info into seperate cells?


    Paul


  8. #8
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a master sheet for each month that contains 31 sheets (one for each day of the month) each sheet contains approx 200 data cells.

    Sepearately, someone produces each month a data file containing all the data information in identical format.I then in the master do many calculations and graphs related to it.

    What I want to do is bring in the relevant months data into all 31 sheets by simply changing the text entry from JanData.xls to FebData.xls

    There is no real problem with the relevant months data file having to be open although it would be nice.

    The bigger problem I see is that the formula will not copy and paste relatively and I am going to have to amend each cell manually (I can you search and replace to an extent)

    what would, onthinking about it be better was if each sheet could link directly to the relevant data sheet in its entirety (they are identical)

    Any further Ideas ?

    [ This Message was edited by: Peter100 on 2002-05-09 07:44 ]

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =INDIRECT("["&$A$1&"data.xls]Sheet1!"&ADDRESS(1,COLUMN(A:A),4,1))

    this may help if you have "jan" "feb" etc as your cells to refer to in A1

    ADDRESS(1 points it to destination row 1
    COLUMN a:a RETURNS "1" AND IS TRANSLATED TO A, assuming your first destination column is A..... just change these two appropriately

    ie if your first bit of info sits in G7, change it to

    &ADDRESS(7,COLUMN(G:G),4,1)

    this should then copy over and down as needed

    downsides :

    1) you may notice recalc times shhot up because of the volatile functions

    2) scource workbooks still need to be open for indirect to work
    :: Pharma Z - Family drugstore ::

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 23:10, Peter100 wrote:
    I have a workbook named Master.xls which is a master template.
    I have workbooks for each month named JanData.xls - Dec.xls

    I want to link the data from cell A1 in JanData.xls into cell A1 of
    Master.xls which is simple enough.

    The link would look like ='[JanData.xls]Sheet1'!$A$1

    But what I want to be able to do is replace the [JanData.xls] part of the link
    with a cell reference in Master.xls which would be a text entry so that the link
    could be changed to read from FebData.xls - MarData.xls etc as desired.

    Anyone any Ideas ?

    I used the HYPERLINK function ...

    path name in cell A1
    File name with extension in cell B1
    sheet name and cell name in cell c1

    Then use the following formula for hyperlink ...

    =HYPERLINK(A1&B1,C1)

    This will work even when you change any of the entries in cells A1 B1,or C1 and even if the workbooks are closed.

    please post back if it works for you ... otherwise explain a little further and let us take it from there.

    Regards!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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
  •