How to autopopulate multiple records (rows) from one drop down list

jaimem00

New Member
Joined
Jan 20, 2013
Messages
8
ISSUE:
I have looked and tried to use the vlookup, hlookup functions and I am unable to get anything started.
I would like to be able to choose a Size from a drop down list, that will autopopulate 1 of 2 lists of products and prices.
The parts are specific to the size.
After choosing the size, the Parts and Price table will populate below the chosen size.
I will manually insert the amount of items I needed for each part
This will calculate the total from the price*quantity, and then sum all the totals.

SIZES
PARTS2-10M2-15M4-10M4-15M5-10M5-15M7-10M7-15M
Gate FC (Manual)600600800xxxxx
Gate (Hyd.)6006000xxxxx
Seat Ring FC250250425xxxxx
Seat Ring Seal FC101010xxxxx
Body Bushing FC250250450xxxxx
Body Bushing Seal FC101010xxxxx
Retainer Plates FC9393250xxxxx
Operating Stem FC650650950xxxxx
Stem Packing FC555563xxxxx
Tail Rod255255285xxxxx
Bearing FC101020xxxxx
Race FC101020xxxxx
Bonnet Gasket FC100100115xxxxx
Gatexxx20002000200030003000
Seatxxx60060060012201220
Seat Seal (ID - OD)xxx5535355555
Operating Stemxxx14001400140021002100
Tail Rodxxx10001000100016001600
Stem Packingxxx220150220220220
Hydraulic Stemxxx20002000310031003100
Retainer Platexxx000785785
Bonnet Gasketxxx265150265501501

<tbody>
</tbody>


EXAMPLE
(SIZE) 7-15M
PartPriceQtyTotal
Gate3000
Seat1220
Seat Seal (ID - OD)55
Operating Stem2100
Tail Rod1600
Stem Packing220
Hydraulic Stem3100
Retainer Plate785
Bonnet Gasket501
TOTAL:

<tbody>
</tbody>
 
Thank you, my error was $B$1 not $B$5.
It works now. Thanks so much.


Is there a way to have the drop down populate a list 9 parts or 13 parts?




 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
No worries, glad you got it sorted

Don't quite understand the second question though?...
 
Upvote 0
Sorry for the confusion.


Example:
A5 Choose Size: 2-15M (according to table, the records returned would be a parts list of 13 items)

A8 Gate FC (Manual)
A9 Gate (Hydraulic)
A10 Seat Ring FC
A11 Seat Ring Seal FC
A12 Body Bushing FC
A13 Body Bushing Seal FC
A14 Retainer Plates FC
A15 Operating Stem FC
A16 Stem Packing FC
A17 Tail Rods
A18 Bearing FC
A19 Race FC
A20 Bonnet Gasket FC

<colgroup><col></colgroup><tbody>
</tbody>

IF
A5 Choose Size: 7-15M (according to table, the records returned would be a parts list of 9 items)

A8 Gate
A9 Seat
A10 Seat Seal (ID-OD)
A11 Operating Stem
A12 Tail Rod
A13 Stem Packing
A14 Hydraulic Stem
A15 Retainer Plate
A16 Bonnet Gasket

<colgroup><col></colgroup><tbody>
</tbody>

Does this make sense?
 
Upvote 0
Ah, I see - eh, yeah, that'll be quite tricky to do I think

Only way I can think of would be to use an array formula

This is as far as I can get but it doesn't work - perhaps somebody else on the forum can finish the job (I'm only getting to grips with array formulas myself...)

In cell A8 :

{=INDEX(OFFSET(Prices,,,,1),MATCH(TRUE,ISNUMBER(OFFSET(Prices,,MATCH($B$5,Sizes,0),,1)),0))}

In cell A9 :

{=INDEX(OFFSET(Prices,,,,1),MATCH(1,(NOT(OFFSET(Prices,,,,1)=$A$8:$A8))*ISNUMBER(OFFSET(Prices,,MATCH($B$5,Sizes,0),,1)),0))}

And then drag to cell A29 (have to use a separate formula for A8 to avoid circular references)

It only seems to work for the first 2 cells, the others either give an error or replicate parts from above (which I hoped the formula would detect and bypass)

When it gets to cell A10 and beyond, the first parameter of the MATCH function returns #N/A's instead of TRUE/FALSE's and I'm not sure why.

Maybe see if one of the MVP's can help (I'd be curious to know myself!!)

Sorry I can't figure it out for you...

AOB
 
Upvote 0
I just kind of put this together and it is working.
Made a different table and range.
1 2345678910111213
2-10M Gate FC (Manual) Gate (Hydraulic)Seat Ring FC Seat Ring Seal FC Body Bushing FC Body Bushing Seal FC Retainer Plates FC Operating Stem FC Stem Packing FCTail Rods Bearing FCRace FCBonnetGasketFC
2-15M Gate FC (Manual)Gate (Hydraulic)Seat Ring FCSeat Ring Seal FCBody Bushing FCBody Bushing Seal FCRetainer Plates FCOperating Stem FCStem Packing FCTail RodsBearing FCRace FCBonnet Gasket FC
4-10M Gate FC (Manual)Gate (Hydraulic)Seat Ring FCSeat Ring Seal FCBody Bushing FCBody Bushing Seal FCRetainer Plates FCOperating Stem FCStem Packing FCTail RodsBearing FCRace FCBonnet Gasket FC
4-15M GateSeatSeat Seal (ID-OD)Operating StemTail RodStem PackingHydraulic StemRetainer PlateBonnet Gasket
5-10MGateSeatSeat Seal (ID-OD)Operating StemTail RodStem PackingHydraulic StemRetainer PlateBonnet Gasket
5-15MGateSeatSeat Seal (ID-OD)Operating StemTail RodStem PackingHydraulic StemRetainer PlateBonnet Gasket
7-10MGateSeatSeat Seal (ID-OD)Operating StemTail RodStem PackingHydraulic StemRetainer PlateBonnet Gasket
7-15MGateSeat Seat Seal (ID-OD) Operating StemTail Rod Stem Packing Hydraulic Stem Retainer PlateBonnet Gasket

<colgroup><col span="15"> </colgroup><tbody>
</tbody>

A5 Choose Size: 2-15M (according to table, the records returned would be a parts list of 13 items)

A8 =VLOOKUP(A5,Data!$L$2:$Y$9,2,FALSE)
A9 =VLOOKUP(A5,Data!$L$2:$Y$9,3,FALSE)
A10 =VLOOKUP(A5,Data!$L$2:$Y$9,4,FALSE)
A11 =VLOOKUP(A5,Data!$L$2:$Y$9,5,FALSE)
A12 =VLOOKUP(A5,Data!$L$2:$Y$9,6,FALSE)
A13=VLOOKUP(A5,Data!$L$2:$Y$9,7,FALSE)
etc
etc

<tbody>
</tbody>

But, when i dont have a ZERO in the QTY, I still get a total. besides that, I am working fine.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top