+ Reply to Thread
Results 1 to 5 of 5

Populate summary table with values from various worksheets

  1. #1
    Registered User
    Join Date
    08-07-2015
    Location
    saskatchewan
    MS-Off Ver
    2010
    Posts
    7

    Populate summary table with values from various worksheets

    I have a workbook with several work sheets. Some worksheets have a project specific table listing materials on order, projected delivery dates, cost, vendor, and purchase order number for that specific project. Not all rows will have a purchase order number. I have a summary worksheet that I want to pull data from the project specific work sheets. I would like the summary table to populate it's rows with the data from the project work sheets, but only the rows that have a PO number. PO number is in the right most column. I do not want the rows without a PO number to be displayed in the summary table.

    I have messed around with Vlookup, index match, and a couple other functions but cannot get them to behave the way I would like. Any suggestions for other formulas to try?

    summary table headings, starting in column C:
    Item / PO Number / Cost / Vendor / Delivery


    Project specific table headings starting in column C:
    Item / Lead Time / Delivery / Cost/ Ordered / Vendor / PO Number
    Last edited by Lyndo; 12-02-2015 at 10:32 AM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,334

    Re: Populate summary table with values from various worksheets

    In general VLOOKUPs work well with data to the right of your lookup value and index match for data to the left. Without better information it is hard to tell what your issue is. Post a sample if you can.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    08-07-2015
    Location
    saskatchewan
    MS-Off Ver
    2010
    Posts
    7

    Re: Populate summary table with values from various worksheets

    Here is a sample (pics and direct link to sample sheet on dropbox)

    A stated in the original post I am looking to have the sheet in the first pic auto populate when an item is entered in the other two sheets, but only if it has a PO number (4501******). All PO numbers will start with a 4501 and the following 6 will be different for each item. the PO column can move, it doesn't need to be on the right side, I think I am going to try moving it to the furthest left column and try using vlookup again. Thanks!

    https://www.dropbox.com/s/1r7fe64ccu...mple.xlsx?dl=0

    http://i19.photobucket.com/albums/b1...ps49f8ewfg.jpg
    http://i19.photobucket.com/albums/b1...pssyjmhchc.jpg
    http://i19.photobucket.com/albums/b1...ps3meoywpa.jpg

    Excel Help 1.JPG
    Excel Help 2.JPG
    Excel Help 3.JPG
    Last edited by Lyndo; 11-30-2015 at 12:55 PM.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,334

    Re: Populate summary table with values from various worksheets

    Ok, here is a general guide. First I would say to get more specific help you should post a sample workbook with no confidential data because nobody wants to retype your data. To do that use the "go advanced" button at the bottom of the post box and follow the prompts.
    Now having said that, if you use an index/match you can apply an iferror to that to return as blank if the PO number isn't found in your other sheets. So if your PO number is the consistent item between sheets, using your summary of purchase order sheet as a foundation and the jpg 14284 as a guide in cell C5, =iferror(index(14284!G:G,match(C5,14284!H:H,0)),"") should return the vendor for your PO number from the sheet you seem to have labeled as 14284. If it doesn't find the PO number it should return a blank. You can follow the same principles for other values to lookup. Hope that helps.

  5. #5
    Registered User
    Join Date
    08-07-2015
    Location
    saskatchewan
    MS-Off Ver
    2010
    Posts
    7

    Re: Populate summary table with values from various worksheets

    Thanks for your help Sambo Kid. I didn't know about the sample workbook through the go advanced option, I'll be sure to use that next time.

+ 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. Macro Help: Auto Populate Summary Table
    By pdowney in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-06-2014, 04:21 PM
  2. [SOLVED] Summary Table generated from Data Worksheets
    By kristy.brown in forum Excel General
    Replies: 8
    Last Post: 07-02-2014, 05:13 PM
  3. Replies: 0
    Last Post: 04-01-2014, 06:42 PM
  4. Replies: 11
    Last Post: 01-13-2014, 05:15 AM
  5. [SOLVED] Need help with a formula to populate a 'summary' table.
    By jimredfield in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 10-31-2013, 05:41 PM
  6. Replies: 2
    Last Post: 05-06-2013, 08:53 AM
  7. Summarizing values from different worksheets to one summary worksheet
    By lkim in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-05-2012, 08:06 AM

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