displaying data in different cells, Depending on the value of 1 cell.

[Luke1690]

Morning,

i have a a part list, where people can select QTY of parts they require.

When the QTY is chosen i need the data displayed in different cells

qty Range B22:B55 Amount that they can order is 1 or 2

When a 1 or 2 is selected in the qty box (B22), i would like cellls A22,B22,C22,D22 to show in cells L9,M9,N9,O9

If a another cell has a qty selected lets say (B24), Cells A24,B24,C24,D24 To show in cells L10,M10,N10,O10

and so on.....

Is this possible?

ive been looking for videos to train my self but not to sure where to look, if someone can point in the right direction.

Thanks guys

 

[Peter_SSs]

please see the below.

Then are you sure that you actually are using MS 365 that your profile shows? FILTER is a standard function in Microsoft 365.

 

[Luke1690]

i didn't even know it was set to 365 im using 2016 i have changed it now. really sorry to waste your time.

 

[Peter_SSs]

i didn't even know it was set to 365

It is not a default setting, so you must have specifically set it at some point. .

im using 2016

In that case try this formula, copied across and down.

Luke1690.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
16												Part	Qty	Desc	Loc
17												Part a	1	Desc 1	Loc 1
18												Part d	2	Desc 4	Loc 4
19	Part	Qty	Desc	Loc
20	Part a	1	Desc 1	Loc 1
21	Part b		Desc 2	Loc 2
22	Part c		Desc 3	Loc 3
23	Part d	2	Desc 4	Loc 4
24	Part e		Desc 5	Loc 5
25	Part f		Desc 6	Loc 6
26	Part g		Desc 7	Loc 7
27
Sheet1

Cell Formulas
Range	Formula
L17:O21	L17	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$20:A$100)/($B$20:$B$100>0),ROWS(L$17:L17))),"")

 

[Luke1690]

Thank you so much and thanks for being patient

 

[Peter_SSs]

You're welcome. Glad we got there in the end.