+ Reply to Thread
Results 1 to 9 of 9

HELP ... Update a worksheet based on raw data in another worksheet

  1. #1
    Registered User
    Join Date
    02-02-2013
    Location
    Madison, WI
    MS-Off Ver
    Excel 2007
    Posts
    3

    HELP ... Update a worksheet based on raw data in another worksheet

    I am hoping someone can help me. I need to finish this project tonight and I am running out of options. I am HUGE newbie, so I will try to explain what I am looking to do.

    I have data in one worksheet. The data is information from companies that are providing specific information about themselves. Each company has a unique identifier, being their supplier code.

    In another worksheet, I have a template that needs to populate using individual company's data. So for instance, if I'm in the template worksheet and I type in the Supplier Code 123, then I need the template to automatically populate with that company's data. Then, If I choose another Supplier Code, 567, then the template needs to update with that company's data.

    I hope I'm making sense. Any help would be GREATLY appreciated.

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: HELP ... Update a worksheet based on raw data in another worksheet

    Without seeing an EXAMPLE (do not need the real data, just the structure of it) of what you are working with, all I can offer is some genaralities..
    Option1: Highlight the data in first sheet, make it a table, then use the filter dropdown on supplier code to display that information..
    Option2: Turn the data into a pivot table and use the filters etc to display your info
    Option3: Use one of the lookup methods (if Supplier code is in the first column of the raw data, VLOOKUP() would work well) to find the supplier code, then return the Data to proper spots in your template...

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    02-02-2013
    Location
    Madison, WI
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: HELP ... Update a worksheet based on raw data in another worksheet

    Thank you so much for reaching out to help. I SOOOOO appreciate it. I've attached an example of what I'm trying to accomplish.

    The T2 worksheet contains the raw data.
    The Spend Report worksheet is the template.

    In the Spend Report worksheet, I need a way to add a drop-down field (have no clue how to do that) in the Supplier Code field (B6), which corresponds to the Supplier Code data in the T2 worksheet.

    When the pull-down menu selects a supplier code, the data in the Spend Report "template" updates with that company's information. Then, when another supplier code is chosen, the data in the Spend Report template updates with that company's data, etc.

    ANY help you can lend would be greatly appreciated. My mind is mush trying to self-teach and I'm running out of time. THANK YOU
    Attached Files Attached Files

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: HELP ... Update a worksheet based on raw data in another worksheet

    This seems to work, but it is a little "in-elegant", see attachment
    I am working on a little better solution, this one requires a lot of hand adjustment to get the columns right, because the row headers in the spend report, do not match column headers...if I can get a worki around built, I will post that

    I also put the Supplier code into sheet3 for the drop down, made the access list 1000 rows long (adjust if necessary)...
    Not sure about the reporting period, hence the highlighted RED row
    And did not know how you wanted to deal with possible duplicate supplier numbers in the data, as it stands, it uses the one at the top of the sheet???

    Hope this helps
    Attached Files Attached Files

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: HELP ... Update a worksheet based on raw data in another worksheet

    As promised, new WB with a much simpler solution :SAMPLEReport.sol3.xlsx

    Sheet 'Spend Report' column A has the formulas that I used to get the headers, I then copied that to sheet 'Spend Report 2'copied column a, pasted special as values, so spend report 2 has the new improved formula..
    In B8:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    drag down, hit the copy drop down and select without formating..

    the two formulas in B4 and B5 remain the same...
    and still awaiting feedback on possible multiple matches??

    Hope this helps

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: HELP ... Update a worksheet based on raw data in another worksheet

    Here's the Table solution, with all selected in B column filter:SAMPLEReport.sol4.xlsx
    I'm still figuring out Pivot Tables my self, so I'm not sure how to offer you that particular solution yet

    Hope this helps

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: HELP ... Update a worksheet based on raw data in another worksheet

    @dredwolf

    I made an pivot table for you.

    Just drag and drop the criteria in the right section.

    Just play around with it; if you know the "rules" of pivot table, you gonna use it more and more and more often (i promise you that).

    Do you have questions or comments on this item, just ask or comment.

    P.s. for a pivot table it's easier if the data are listed in rows (e.g. all values in column f, instead all data in columns (per row)).

    There are macro's available to re-arrange the data.
    Attached Files Attached Files
    Last edited by oeldere; 02-03-2013 at 05:38 AM.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  8. #8
    Registered User
    Join Date
    02-02-2013
    Location
    Madison, WI
    MS-Off Ver
    Excel 2007
    Posts
    3

    Cool Re: HELP ... Update a worksheet based on raw data in another worksheet

    All I can say is THANK YOU from the bottom of my heart! This seems to be the solution I was killing myself to come up with yesterday. I have nowhere near the expertise you have and you have truly been a Blessing to me!

    I'm going to tinker with this and then come back here to give you proper kudos and mark this threat Solved.

    THANK YOU


  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: HELP ... Update a worksheet based on raw data in another worksheet

    @oeldere Thanks for the pivot table

    @melanieokane Glad you have found something to work for you
    Last edited by dredwolf; 02-03-2013 at 12:15 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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