Results 1 to 8 of 8

Thread: Generating a kits contents with a formula

  1. #1

    Generating a kits contents with a formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    I am new to the thread any advice would be greatly appreciated. I am not sure if this is possible but on the attached sheet under "repair Kit" when part number R123456 is in putted in that cell, in the next cell labeled "kit contents" it would bring up a list of what is included in that kit. See sheet 2 for the contents of the kit. Any ideas. I have tried formulas but to no avail and am not famaliar enough with VBE yet. Thanks!
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi there,

    Can you upload a sample that has about 15 rows of data in the parts listing page (Sheet 1)

    I'm thinking you'd be best to use a PivotTable to quickly generate your list, but need to see a bit more of your source data. (Mock data is fine, as long as the format is consistent.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Roche pump sheet.xlsHi Ken,
    I have tried pivot tables. they don't seem to meet the need I am requiring. I need more of a pop up sort of thing. Also I will be passing this excel sheet along to a customer and I am not sure how tech savy they are. Thank you for your help. I attached another sheet with some dummy info.

  4. #4
    Think of cars make and models each car's VIN number tells what the car is made of. I want the customer to see what is specific to each pump, when they input the kit number if that helps.

  5. #5
    Here is the revised sheet. This is along the lines of detailing what a car is made of by the "vin" number, if that helps. I tried pivot tables and they semed more along the lines of sorting through and compiling "like" data. Each pump will have specific information to that pump only. Let me know and thanks for your help.
    Attached Files Attached Files

  6. #6
    Put this ARRAY formula into I2

    =MIN(IF(RIGHT(Sheet2!A1:C1,LEN(Sheet1!H2))=Sheet1!H2,COLUMN(Sheet2!A1:C1)))

    and copy down

    Put this ARRAY formula into J2

    =IF(INDEX(Sheet2!$A:$C,COLUMN(A1)+1,$I2)<>0,INDEX(Sheet2!$A:$C,COLUMN(A1)+1,$I2),"")

    and copy down and across

  7. #7
    Rev sheet pump sheet.xlsI thought I replied to this last month but guess cyber space must have got it... Here is the mock sheet. I really hope you have a trick up your sleeve as I am about at my wits end with this.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    BPound,

    I apologize for this, but for some reason not only did your last two posts get caught in the spam filter, but Bob's reply as well. I've approved them now, so hopefully Bob's answer will help. If not, let us know.

    Again, sorry about this! The spam filter got a little aggressive for some reason.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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