Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    WARREN, Ohio, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel workbooks (windows xp)

    I have two workbooks, I need information from one workbook to go into the second and in the second find the proper worksheet to be input the data. Can this be done or am i asking for way too much

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: excel workbooks (windows xp)

    Sounds like it can be done, but can you explain in more detail? Do you want a macro to repeat the process regularly or is this a one-time requirement?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    WARREN, Ohio, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel workbooks (windows xp)

    it will probally work better with a macro (I never thought of doing it that way), but it's an on going situation. Taking information off of Workbook #1 (Purchase Orders) and appling it to the correct page in Workbook #2 (Budget).

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: excel workbooks (windows xp)

    Coding this accurately depends on the names and locations of the Workbooks and Worksheets and Ranges involved, so It's hard to demonstrate exactly what to do without a sample setup from you. Legare's posts in the thread starting <!post=here,75127>here<!/post> do an excellent job of showing how to do what you want (maybe more complicated than you want), and if you are not familiar with working with Macros, see also his <!post=Star Post,118382>Star Post<!/post> on Personal.xls. If you can post some simplified samples, after removing any confidential data, someone can help you in more detail.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: excel workbooks (windows xp)

    My afternoon got a little slow <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, so see the attached. Note the use of "Application.DefaultFilePath" as assumed location of the purchase order file; you'll have to adjust this as necessary. Not extensively tested, but should give you a good start.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Location
    WARREN, Ohio, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel workbooks (windows xp)

    Thanks for helping but this is way over my head... I'm sending you a copy of the Purchase order (hightlighted orange is the information that I need to go into the budget workbook.... the green "code" is the page it needs to go on. My Budget pages are also attached. Thank you for all the Help... I really think that this is simple enough... but too complex for my experience with excel. Hope this helps
    Attached Files Attached Files

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

    Re: excel workbooks (windows xp)

    I tried to fathom what would go where on your pages, but failed <g>.

    Is it OK if you use a direct link (so the cell in the "receiving" sheet is always linked to the exact same cell in the Source sheet)?

    In that case, click on the cell that needs to "receive" information, press the equal sign, then navigate to the workbook, sheet and cell that contains the information, click on that cell and hit Enter. Now you should see a formula like this:

    ='[my purchase order.xls]JOHN''S PAINT'!$B$2
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    New Lounger
    Join Date
    Oct 2002
    Location
    WARREN, Ohio, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel workbooks (windows xp)

    No not always true. The Recieving Workbook will have to find the correct code page to insert the purchase order items. Does this sound better? By the way thank you for your help.

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

    Re: excel workbooks (windows xp)

    Could you try and tell us on what criteria one (Excel) would decide where to look for the data?

    Is the data always on the same sheet?
    If not, is it always on the same cell(s), but in a different sheet?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    New Lounger
    Join Date
    Oct 2002
    Location
    WARREN, Ohio, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel workbooks (windows xp)

    The Data is always in the same cells on the Purchase Order Workbook ... but different sheets. The code (Green) for that particular budget item would have to be found in the Buget workbook and find that code worksheet. ????

  11. #11
    New Lounger
    Join Date
    Oct 2002
    Location
    WARREN, Ohio, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel workbooks (windows xp)

    The Data is always in the same cells on the Purchase Order Workbook ... but different sheets. The code (Green) for that particular budget item would have to be found in the Buget workbook and find that code worksheet. ????

  12. #12
    New Lounger
    Join Date
    Oct 2002
    Location
    WARREN, Ohio, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel workbooks (windows xp)

    sorry look on the string for answers... getting confused again!

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

    Re: excel workbooks (windows xp)

    I assume the name of the sheet you need to look in is directly to the left of the cell that now contains APT (the cells c9 and d9 on sheet APT in the workbook called "my budget actual.xls")?

    Put this formula (copy from this message) into cell D9:

    =INDIRECT("'[my purchase order.xls]" &C9&"'!$B$42")

    BUT!!!! remove the apostrophe from the sheetname of the other workbook (and from the cell C9), it causes trouble with this method.

    If you need info from another cell, simply change the address ($B$42) in this formula.
    If you need another sheet, simply type it's name into cell C9.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    New Lounger
    Join Date
    Oct 2002
    Location
    WARREN, Ohio, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel workbooks (windows xp)

    THANK YOU AND I UNDERSTAND AND CAN DO THIS. WILL TELL YOU LATER THIS AFTERNOON WHEN I CAN EXECUTE ALL OF THIS. THANK YOU ...... THANK YOU

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: excel workbooks (windows xp)

    Hi Moe,
    A few more questions:
    1. Do you automatically want every item from every sheet in the purchase order to be transferred across? (can there be more than one row per sheet in the purchase order?)
    2. Is the data always in the same place on each sheet?
    3. What goes into which column on the Budget sheets? I assume the $1000 from John's paint in your example goes into cell E12 on sheet APT in your budget workbook, but what goes in the other columns?
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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