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

[jaimem00]

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.

[TABLE="width: 662"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 8"]SIZES[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]PARTS[/TD]
[TD]2-10M[/TD]
[TD]2-15M[/TD]
[TD]4-10M[/TD]
[TD]4-15M[/TD]
[TD]5-10M[/TD]
[TD]5-15M[/TD]
[TD]7-10M[/TD]
[TD]7-15M[/TD]
[/TR]
[TR]
[TD]Gate FC (Manual)[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]800[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Gate (Hyd.)[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Seat Ring FC[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]425[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Seat Ring Seal FC[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Body Bushing FC[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]450[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Body Bushing Seal FC[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Retainer Plates FC[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]250[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Operating Stem FC[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"]950[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Stem Packing FC[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]63[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Tail Rod[/TD]
[TD="align: right"]255[/TD]
[TD="align: right"]255[/TD]
[TD="align: right"]285[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Bearing FC[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Race FC[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Bonnet Gasket FC[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]115[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Gate[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD]Seat[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1220[/TD]
[TD="align: right"]1220[/TD]
[/TR]
[TR]
[TD]Seat Seal (ID - OD)[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Operating Stem[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]2100[/TD]
[TD="align: right"]2100[/TD]
[/TR]
[TR]
[TD]Tail Rod[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1600[/TD]
[TD="align: right"]1600[/TD]
[/TR]
[TR]
[TD]Stem Packing[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]220[/TD]
[/TR]
[TR]
[TD]Hydraulic Stem[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3100[/TD]
[TD="align: right"]3100[/TD]
[TD="align: right"]3100[/TD]
[/TR]
[TR]
[TD]Retainer Plate[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]785[/TD]
[TD="align: right"]785[/TD]
[/TR]
[TR]
[TD]Bonnet Gasket[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]501[/TD]
[/TR]
</tbody>[/TABLE]


EXAMPLE
[TABLE="width: 365"]
<tbody>[TR]
[TD] (SIZE) 7-15M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Price[/TD]
[TD]Qty[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Gate[/TD]
[TD="align: right"]3000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Seat[/TD]
[TD="align: right"]1220[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Seat Seal (ID - OD)[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Operating Stem[/TD]
[TD="align: right"]2100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tail Rod[/TD]
[TD="align: right"]1600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stem Packing[/TD]
[TD="align: right"]220[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hydraulic Stem[/TD]
[TD="align: right"]3100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Retainer Plate[/TD]
[TD="align: right"]785[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bonnet Gasket[/TD]
[TD="align: right"]501[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jaimem00]

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?

 

[AOB]

No worries, glad you got it sorted

Don't quite understand the second question though?...

 

[jaimem00]

Sorry for the confusion.


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

[TABLE="width: 173"]
<colgroup><col></colgroup><tbody>[TR]
[TD] A8 Gate FC (Manual)[/TD]
[/TR]
[TR]
[TD] A9 Gate (Hydraulic)[/TD]
[/TR]
[TR]
[TD]A10 Seat Ring FC[/TD]
[/TR]
[TR]
[TD]A11 Seat Ring Seal FC[/TD]
[/TR]
[TR]
[TD]A12 Body Bushing FC[/TD]
[/TR]
[TR]
[TD]A13 Body Bushing Seal FC[/TD]
[/TR]
[TR]
[TD]A14 Retainer Plates FC[/TD]
[/TR]
[TR]
[TD]A15 Operating Stem FC[/TD]
[/TR]
[TR]
[TD]A16 Stem Packing FC[/TD]
[/TR]
[TR]
[TD]A17 Tail Rods[/TD]
[/TR]
[TR]
[TD]A18 Bearing FC[/TD]
[/TR]
[TR]
[TD]A19 Race FC[/TD]
[/TR]
[TR]
[TD]A20 Bonnet Gasket FC[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 174"]
<colgroup><col></colgroup><tbody>[TR]
[TD] A8 Gate[/TD]
[/TR]
[TR]
[TD] A9 Seat[/TD]
[/TR]
[TR]
[TD]A10 Seat Seal (ID-OD)[/TD]
[/TR]
[TR]
[TD]A11 Operating Stem[/TD]
[/TR]
[TR]
[TD]A12 Tail Rod[/TD]
[/TR]
[TR]
[TD]A13 Stem Packing[/TD]
[/TR]
[TR]
[TD]A14 Hydraulic Stem[/TD]
[/TR]
[TR]
[TD]A15 Retainer Plate[/TD]
[/TR]
[TR]
[TD]A16 Bonnet Gasket[/TD]
[/TR]
</tbody>[/TABLE]

Does this make sense?

 

[AOB]

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

 

[jaimem00]

I just kind of put this together and it is working.
Made a different table and range.

1

2

3

4

5

6

7

8

9

10

11

12

13

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 FC

Tail Rods

Bearing FC

Race FC

2-15M

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 FC

Tail Rods

Bearing FC

Race FC

4-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 FC

Tail Rods

Bearing FC

Race FC

4-15M

Gate

Seat

Seat Seal (ID-OD)

Operating Stem

Tail Rod

Stem Packing

Hydraulic Stem

Retainer Plate

5-10M

Gate

Seat

Seat Seal (ID-OD)

Operating Stem

Tail Rod

Stem Packing

Hydraulic Stem

Retainer Plate

5-15M

Gate

Seat

Seat Seal (ID-OD)

Operating Stem

Tail Rod

Stem Packing

Hydraulic Stem

Retainer Plate

7-10M

Gate

Seat

Seat Seal (ID-OD)

Operating Stem

Tail Rod

Stem Packing

Hydraulic Stem

Retainer Plate

7-15M

Gate

Seat

Seat Seal (ID-OD)

Operating Stem

Tail Rod

Stem Packing

Hydraulic Stem

Retainer Plate

<colgroup><col span="15"> </colgroup><tbody>
[TD="colspan: 2"]BonnetGasketFC[/TD]

[TD="colspan: 2"]Bonnet Gasket FC[/TD]

[TD="colspan: 2"]Bonnet Gasket FC[/TD]

[TD="colspan: 2"]Bonnet Gasket[/TD]

[TD="colspan: 2"]Bonnet Gasket[/TD]

[TD="colspan: 2"]Bonnet Gasket[/TD]

[TD="colspan: 2"]Bonnet Gasket[/TD]

[TD="colspan: 2"]Bonnet Gasket[/TD]

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