+ Reply to Thread
Results 1 to 12 of 12

3 tables, 3 tabs => information needed in 1 sheet

  1. #1
    Registered User
    Join Date
    10-23-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    3 tables, 3 tabs => information needed in 1 sheet

    Dear all,

    I have 3 tables, which are in 3 different tabs. (called PM, jobplan, jobtask).
    A PM has 1 jobplan and a jobplan can have multiple jobtasks.

    This information I would like to show in 1 sheet. (see attachment where this is possible in Access via forms).
    I tried already in Excel 2013 the relations in pivot table but this did not work out. The pivot table returns 1 jobplan but all the values available in the table jobtasks, not only the ones relevant for that plan.

    I don't mind using VBA, I just would like to work this out in Excel because the customer only works in Excel, not in Access.



    Jobplan contains jobtask.png

    Kind regards,
    Laura

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    There's not much point in attaching a .png file, as it can't be worked on, and many contributors are not able to open those files anyway on this forum due to incompatibilities with certain browsers. Attach a sample workbook instead.

    Pete

  3. #3
    Registered User
    Join Date
    10-23-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    Excel with 1 example has been attached.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    It would help if you also showed how you would want that example data to be shown on the new sheet (and also a few more examples).

    Pete

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    You can add the Pivot Table Wizard to the ribbon of Excel 2010 so that you can combine several ranges from different tabs to create a Pivot Table. Here is an example using your data:.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Registered User
    Join Date
    10-23-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    Attachment with requested result in last tab added. How it looks like is not really important. All information on 1 tab is the main point.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-23-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    Hi newdoverman,

    This is not what I'm looking for. Please have a look at the 2nd attachment with requested result. A user needs to see the list of PM with his jobplan and their tasks in 1 sheet.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    If all the customer needs is on the Requested Result worksheet, can you not export that report from Access to Excel rather than trying to assemble the result from Access tables imported into Excel?

  9. #9
    Registered User
    Join Date
    10-23-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    Dear newdoverman,
    Export a form out of Access as the first comment on this topic is not possible. I only get the first level... (jpnum & description, not the level below jpnum & jptask) so this information I can pull with a vlookup from the sheets.... What I need is the relationship between jpnum & jptask and add them in 1 sheet....

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    You misunderstand. You don't want to export the "Form" you export the data that populates the form. In Access, there should be the ability to query each table of the database and export the results of that query. According to this Office support article, you should be able to do exactly that. https://support.office.com/en-US/art...B-7CE4045FF194

  11. #11
    Registered User
    Join Date
    10-23-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    Dear All,

    I just made 2 queries: query to map jobplan with jobtask. Use this query to create another one to map the PM.
    Then I have the requested information.

    Thanks all for the input.

    Laura

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: 3 tables, 3 tabs => information needed in 1 sheet

    Thank you for the feedback.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. move information in different tables in sheet1 to a calculation in sheet 2
    By Kim-Aleksander in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-11-2015, 05:03 AM
  2. Replies: 9
    Last Post: 07-07-2015, 08:51 AM
  3. Replies: 45
    Last Post: 03-12-2015, 12:46 PM
  4. [SOLVED] New to macro's...have information on several tabs that i'd like pulled into one sheet
    By karryan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2014, 04:36 PM
  5. Populate information sheet tabs from data in table on first tab
    By Chrisb_009 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-19-2014, 03:26 PM
  6. How Do I split a file into several Tabs, acording to information on other tabs?
    By Edinson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2013, 05:17 AM
  7. Replies: 5
    Last Post: 12-11-2012, 05:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1