+ Reply to Thread
Results 1 to 6 of 6

Pulling a value from a sheet with multiple possible part numbers.

  1. #1
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    Pulling a value from a sheet with multiple possible part numbers.

    I have a sheet (its the first snip at the top of the example attachment) showing two part number columns, an item column, and the amount I requested. The two part number columns can have one or two numbers depending on where the item might ship from. in the case of only one number either one of the columns can be blank.

    The vendor sends a sheet with pn, item name, and qty ordered. In a day or two they'll send an identical sheet except that the last column will say shipped instead of ordered. Each of the vendors sheets occupies a tab behind my worksheet.

    I need to lookup the part number of the vendor item and populate the ordered, and shipped columns on my sheet. That way I can see the progression of the numbers. Conditional formatting allows me to quickly spot shortages etc. This workbook is generated weekly. Ideally I'd like to open last weeks book, change my request numbers and paste over the other two tabs as the vendor sends the confirmation worksheets to me. Then save as a new workbook with current date. The current version has helper columns which sometimes don't work quite right. My IT department prefers that we not use macros or code so that we can hand out the sheets to other people for review or discussion without them having to know more than the absolute basics. You can't lookup on part name because they will never match. So I need to match the part number of the ordered or shipped part regardless of it's position (col A or B) in the request sheet.

    Each sheet will be about 700 lines.

    I attached a small example. I appreciate any ideas....
    Attached Files Attached Files

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Pulling a value from a sheet with multiple possible part numbers.

    can a formula like this one in cell E2 might help you?
    Please Login or Register  to view this content.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pulling a value from a sheet with multiple possible part numbers.

    To be honest I think your layout is making the task more difficult than it needs to be. In order to be able to analyse and report data efficiently it needs to be in a normalised 2 dimensional regular table.

    I suggest you have a table with columns for:

    Date
    Part Number
    Item Name
    Qty Requested
    Qty Ordered
    Qty Shipped

    And I think it would be even better if you were able to give each requisition a unique number and the vendor could quote that Req. number when they send in their Ordered and shipped details. this should enable you to marry up the data and enable you to create reports for things like missing items etc.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    Re: Pulling a value from a sheet with multiple possible part numbers.

    I'm going to give that a try tomorrow. Thanks.

    That was to p24
    Last edited by Mr_Phil; 11-17-2015 at 12:21 AM.

  5. #5
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    Re: Pulling a value from a sheet with multiple possible part numbers.

    Richard, I'm am an access guy working for excel fans. I don't have full control of that sheet. So, changing the layout is considered extreme. But I get your drift and concur. If I can't finale it with formula... I might add a hidden sheet to flatten the initia Sheet and pull the results that way.

  6. #6
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    Re: Pulling a value from a sheet with multiple possible part numbers.

    Quote Originally Posted by p24leclerc View Post
    can a formula like this one in cell E2 might help you?
    Please Login or Register  to view this content.
    That did it. A bit of editing to encompass the actual data and it worked great. I will have to study it to glean HOW it works. But for right this minute I am a happy guy. 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. Replies: 3
    Last Post: 09-23-2014, 04:48 PM
  2. Pulling separate sums from multiple numbers in the same column
    By newskin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-09-2014, 12:18 PM
  3. Calculating Max-Mix for multiple part numbers
    By bgk7472 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-17-2013, 12:08 PM
  4. Overall sheet pulling in info from multiple workbooks
    By Seancsn in forum Excel General
    Replies: 4
    Last Post: 04-10-2013, 10:02 AM
  5. Replies: 8
    Last Post: 02-27-2013, 05:36 PM
  6. VBA? Formula for pulling multiple dates for one part. Please help
    By bernice620 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-13-2012, 11:23 AM
  7. Matching up multiple part numbers with single price
    By sam_m in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-05-2010, 11:55 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