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.

	SIZES
PARTS	2-10M	2-15M	4-10M	4-15M	5-10M	5-15M	7-10M	7-15M
Gate FC (Manual)	600	600	800	x	x	x	x	x
Gate (Hyd.)	600	600	0	x	x	x	x	x
Seat Ring FC	250	250	425	x	x	x	x	x
Seat Ring Seal FC	10	10	10	x	x	x	x	x
Body Bushing FC	250	250	450	x	x	x	x	x
Body Bushing Seal FC	10	10	10	x	x	x	x	x
Retainer Plates FC	93	93	250	x	x	x	x	x
Operating Stem FC	650	650	950	x	x	x	x	x
Stem Packing FC	55	55	63	x	x	x	x	x
Tail Rod	255	255	285	x	x	x	x	x
Bearing FC	10	10	20	x	x	x	x	x
Race FC	10	10	20	x	x	x	x	x
Bonnet Gasket FC	100	100	115	x	x	x	x	x
Gate	x	x	x	2000	2000	2000	3000	3000
Seat	x	x	x	600	600	600	1220	1220
Seat Seal (ID - OD)	x	x	x	55	35	35	55	55
Operating Stem	x	x	x	1400	1400	1400	2100	2100
Tail Rod	x	x	x	1000	1000	1000	1600	1600
Stem Packing	x	x	x	220	150	220	220	220
Hydraulic Stem	x	x	x	2000	2000	3100	3100	3100
Retainer Plate	x	x	x	0	0	0	785	785
Bonnet Gasket	x	x	x	265	150	265	501	501

<tbody>
</tbody>

EXAMPLE

(SIZE) 7-15M
Part	Price	Qty	Total
Gate	3000
Seat	1220
Seat Seal (ID - OD)	55
Operating Stem	2100
Tail Rod	1600
Stem Packing	220
Hydraulic Stem	3100
Retainer Plate	785
Bonnet Gasket	501
TOTAL:

<tbody>
</tbody>

 

[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

A A5 (SIZE) 7-15M	B	C	D
A6
A7 Part	Price	Qty	Total
A8 Gate	3000
A9 Seat	1220
A10 Seat Seal (ID - OD)	55
A11 Operating Stem	2100
etc xxx etc xxx etc xxx A20 xxx A21 Total

<tbody>
</tbody>

 

[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

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1		2-10M	2-15M	4-10M	4-15M	5-10M	5-15M	7-10M	7-15M
2	Gate FC (Manual)	600	600	800	x	x	x	x	x
3	Gate (Hyd.)	600	600		x	x	x	x	x
4	Seat Ring FC	250	250	425	x	x	x	x	x

<TBODY>
</TBODY>

Form sheet :

	A​	B​	C​	D​
1
2
3
4
5	(SIZE)	7-15M dropdown
6
7	Part	Price	Qty	Total
8	Gate	3000
9	Seat	1220
10	Seat Seal (ID - OD)	55
11	Operating Stem	2100

<TBODY>
</TBODY>

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