Generating a kits contents with a formula

[Bpound]

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!

[Ken Puls]

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.)

[Bpound]

565Hi 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.

[Bpound]

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.

[Bpound]

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.

[Bob Phillips]

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

[Bpound]

585I 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.

[Ken Puls]

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.