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]

 

[AOB]

Hi jaimem00

This should be possible but with some assumptions & caveats. This should get you started though...

I presume you have your core data (i.e. the list of prices per part & size) on one sheet, starting at A1. Let's call that sheet 'Data'. Select the range of cells containing that table of information and name it 'Prices'. Also, select the range of cells containing the different sizes (i.e. row 2 based on what you have pasted below) and name it 'Sizes'

Next, I presume you have your 'form' which you want to populate with the data on a separate sheet. Let's call that sheet 'Form'. Select the cell where you want your dropdown (to select the size) and do the following :

Data > Data Validation... > Settings > Validation criteria

Allow: List​

Source: "=Sizes"
​

This should give you your dropdown (I've used cell B1)

Next, under your price column, use the following formula

=VLOOKUP($A4,Prices,MATCH($B$1,Sizes,0)+1,FALSE)

In other words, look up the part in column A in the 'Prices' named range, and return the value in the column matching the size provided by the dropdown (the extra +1 accounts for the fact that the sizes start in column B, not column A...) Note that I've anchored the reference cell for the size ($B$1) so it won't shift as you drag the formula down / across.

Bear in mind that for this to work properly, you should really have unique names for all your parts (as the VLOOKUP will return the first instance it finds) - so there will be a conflict with the likes of "Tail Rod" which you have in twice.

But should be enough to get you started?

AOB

 

[jaimem00]

I will attempt this and get back to you. Thanks.

 

[jaimem00]

Thanks again.
However, I am getting an error under 'Price' column, where I put the vlookup fxn.
I have my drop down on sheet 'Form' in cell 'A5'.

To make sure I have created the 'Ranges' correctly; I dont have the words Parts or Sizes like above.
B1:I1 are my different Sizes
A2:A23 are my diffrent Parts (all uniquely named, just added an 's' to the first 'Tail Rod').

Range 'Prices' = A1:I23
Range 'Sizes' = B1:I23

 

[AOB]

Your 'Sizes' range should only cover the size categories, not any of the corresponding values underneath

So Range 'Sizes' should be = B1:I1 (not B1:I23)

 

[AOB]

You can/should add a handler for scenarios where the part is not recognised or if no size has been specified by the dropdown (show as blank rather than #N/A)

=IF(ISERROR(VLOOKUP($A4,Prices,MATCH($B$1,Sizes,0)+1,FALSE)),"",VLOOKUP($A4,Prices,MATCH($B$1,Sizes,0)+1,FALSE))

Other than that, it should work (make sure you have selected a size from the dropdown!)

 

[jaimem00]

I corrected the range for 'Sizes'.

But, I am still getting an error for the price. Should I be?
here is how my table is created, then the drop down starts again at F5
[TABLE="class: cms_table, width: 365"]
<tbody>[TR]
[/TR]
[TR]
[TD] A
A5 (SIZE) 7-15M[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD] A6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] A7 Part[/TD]
[TD]Price[/TD]
[TD]Qty[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD] A8 Gate[/TD]
[TD="align: right"]3000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] A9 Seat[/TD]
[TD="align: right"]1220[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A10 Seat Seal (ID - OD)[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A11 Operating Stem[/TD]
[TD="align: right"]2100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc xxx
etc xxx
etc xxx
A20 xxx
A21 Total[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AOB]

Bear in mind, my formula assumes the dropdown is in cell B1 and the part name is in column A :

=IF(ISERROR(VLOOKUP($A8,Prices,MATCH($B$1,Sizes,0)+1,FALSE)),"",VLOOKUP($A8,Prices,MATCH($B$1,Sizes,0)+1,FALSE))

You may need to modify it to suit if you have multiple dropdowns for different sections, or if you have your table laid out differently?

 

[jaimem00]

Yes sir, I changed A4 to A8 originally.
However, when I drop down and select a size. I don't have anything returned.
I have my form and data sheet just as stated above.

 

[AOB]

For all of the parts or just some specific ones? (If specific, are they definitely referenced on your lookup table, and within the range defined by 'Prices'?)

And do you have calculations turned on? (Formulas > Calculation > Calculation Options > Automatic)

It works fine for me...

Data sheet :

"Sizes" = B1:I1
"Prices" = A1:I23

[TABLE="class: grid, width: 800"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/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]2
[/TD]
[TD]Gate FC (Manual)
[/TD]
[TD]600
[/TD]
[TD]600
[/TD]
[TD]800
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Gate (Hyd.)
[/TD]
[TD]600
[/TD]
[TD]600
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Seat Ring FC
[/TD]
[TD]250
[/TD]
[TD]250
[/TD]
[TD]425
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[/TR]
</TBODY>[/TABLE]


Form sheet :

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD](SIZE)
[/TD]
[TD]7-15M dropdown
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Part
[/TD]
[TD]Price
[/TD]
[TD]Qty
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Gate
[/TD]
[TD="align: right"]3000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Seat
[/TD]
[TD="align: right"]1220
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Seat Seal (ID - OD)
[/TD]
[TD="align: right"]55
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Operating Stem
[/TD]
[TD="align: right"]2100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Formula in B8 :

=IF(ISERROR(VLOOKUP($A8,Prices,MATCH($B$5,Sizes,0)+1,FALSE)),"",VLOOKUP($A8,Prices,MATCH($B$5,Sizes,0)+1,FALSE))