Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Best way to Link Workbooks

  1. #1
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    479
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi All You Excel People,

    Here is an example of my Master Workbook:
    Most is column headings, the two numbers go under Project Number, I can have many projects. (It doesnt look good when I submit it!)

    FY 2002 CUMULATIVE COST ANALYSIS
    MONTH BRANCH BRANCH PROJECT PROJECT
    NUMBER CHIEF NAME NUMBER
    --------------------------------------------- 7501 0251
    "

    I can go in and enter the Project Number in each row using another worksheet that holds the project numbers. There is also seperate workbooks for each Project that contains the other information that I need (plus more).
    I need a way to move down the worksheet thru the Project Number column and use this
    Project Number to open its appropriate workbook and retrieve the values from the Wkb
    and enter them in the cells then move down to the next Project.


    Has anyone done anything similar to this?

    Your help would be appreciated. Thanks

    [ This Message was edited by: HunterN on 2002-04-23 07:21 ]

  2. #2
    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

    On 2002-04-23 07:19, HunterN wrote:
    Hi All You Excel People,

    Here is an example of my Master Workbook:
    Most is column headings, the two numbers go under Project Number, I can have many projects. (It doesnt look good when I submit it!)

    FY 2002 CUMULATIVE COST ANALYSIS
    MONTH BRANCH BRANCH PROJECT PROJECT
    NUMBER CHIEF NAME NUMBER
    --------------------------------------------- 7501 0251
    "

    I can go in and enter the Project Number in each row using another worksheet that holds the project numbers. There is also seperate workbooks for each Project that contains the other information that I need (plus more).
    I need a way to move down the worksheet thru the Project Number column and use this
    Project Number to open its appropriate workbook and retrieve the values from the Wkb
    and enter them in the cells then move down to the next Project.


    Has anyone done anything similar to this?

    Your help would be appreciated. Thanks

    [ This Message was edited by: HunterN on 2002-04-23 07:21 ]
    so you'd have two seperate workbooks called "7501" and "0251" which hold the information you need on those rows ?

    etc etc etc ......

    (if anyone picks up on this post after I've gone to bed here on GMT time, I'm thinking =INDIRECT AND =VLOOKUP)

  3. #3
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    479
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Various workbooks are all named as the Project Number (ex: 0251.xls, 0751.xls, etc.). These workbooks contain information such as the Project Number Project Name, branch chief and alot of financial columns.

    When I create the new Master Spreadsheet the rows are data being pulled in from each Project workbook. I need a way to open a Project wkbk and grab the data from the spreadsheet and move down one row on the master spreadsheet and open the next project.

    the current formula is ='C:My Project

  4. #4
    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

    Nancy,

    Foolishly, I was going to suggest a simple solution using =INDIRECT and the normal =VLOOKUP that we all love

    however, =INDIRECT only likes workbooks that are open and my guess is that you would like this to work even when you don't have all those project workbooks open

    would I be right ?


  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris Vlookup can look inside a closed workbook can it not?


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    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

    yes, but we need to specify which workbook and be able to copy the formula down....

    as the workbooks are different names, we need to use indirect, at which point we fail cos they may be closed

    Nancy wants to use a project number to open a workbook with that number in the name

    VLOOKUP will do, but you'd have to type it in each time.....

    indirect would solve it, but not if the book's closed

    I'm stumped on this one mate !!


  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-24 15:45, Chris Davison wrote:
    yes, but we need to specify which workbook and be able to copy the formula down....

    as the workbooks are different names, we need to use indirect, at which point we fail cos they may be closed

    Nancy wants to use a project number to open a workbook with that number in the name

    VLOOKUP will do, but you'd have to type it in each time.....

    indirect would solve it, but not if the book's closed

    I'm stumped on this one mate !!

    [img]/board/images/smiles/icon_frown.gif[/img]
    Chris,

    I shouldn't get involved again in this thread, but this must be doable.

    Suppose that in the Main WB, I have a lookup value in A1 in sheet Collect and I want to look it up in A1 in Table1 in WB1.

    Create a worksheet in Project1.xls, say BBoard, and

    in A1 enter:

    =[Main.xls]Collect!A1

    in B1 enter:

    =VLOOKUP(A1,Table1,2,match-type)

    Now,

    in B1 in Collect of Main enter:

    =[Project1.xls]BBoard!B1

    You can generalize this to N project files that can stay closed.

    I proposed this scheme to circumvent the problem of not being able to use SUMIF formulas that refer to closed workbooks.

    Note. This idea is copy-righted. It can be freely used as long as it's recognized as such.

    Aladin

  8. #8
    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

    Yeah, I'd been trying to use the values already in the list which gave rise to the respective workbooks needing to be accessed, without having to further type them in manually.

    No problem with you stepping into the thread, at least the poster has the best way forward now.



    [You've probably spotted I don't like resorting to manual entries though ! There must be a way to use this existing value in a cell to link to it's respective worksheet. One for a rainy day maybe ?!]

    thanks though, and copyright noted

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a very common question.. i hope you can try code below.


    Sub assignvalue()
    Dim assign As String
    Application.ScreenUpdating = False
    Workbooks.Open Filename:="c:my documentsCallingFileName.xls"
    Workbooks("CallingFileName").Activate
    Worksheets("sheet1").Select
    assign = Range("a9").Value
    Workbooks("nameofyourbook").Activate
    Worksheets("Sheet1").Select
    Range(b1).Value = assign
    Workbooks("CallingFileName").Close
    Application.ScreenUpdating = True

    End Sub


    ni****h desai

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a very common question.. i hope you can try code below.


    Sub assignvalue()
    Dim assign As String
    Application.ScreenUpdating = False
    Workbooks.Open Filename:="c:my documentsCallingFileName.xls"
    Workbooks("CallingFileName").Activate
    Worksheets("sheet1").Select
    assign = Range("a9").Value
    Workbooks("nameofyourbook").Activate
    Worksheets("Sheet1").Select
    Range(b1).Value = assign
    Workbooks("CallingFileName").Close
    Application.ScreenUpdating = True

    End Sub


    ni****h desai
    http://www.pexcel.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
  •