Data validation to show name of group.

S

smallxyz

Active Member

Joined

Jul 27, 2015

Messages

392

Office Version

1. 2021

Platform

1. Windows

• Nov 30, 2015

• #2

Are "A2 B2 C2 D2" the group names?
And what are the cell positions where you could select from drop down lists?

 

Upvote 0

E

everblazing

Board Regular

Joined

Sep 18, 2015

Messages

156

• Nov 30, 2015

• #3

no A2, B2, C2, D2 are the cells position for drop down.

the names are for example "Liftup", "Booster", "Gymman".

in sheet2.

categoryA
Fruit
apple
kiwi
orange

Vegetables
Celery
Carrot

Nutts
Almonds
Peanuts
Hazelnuts


So i want to be able to select a mix of these ingrediants which will show me what name of the mix is in D2 sheet1.

I hope this helps

 

Upvote 0

S

smallxyz

Active Member

Joined

Jul 27, 2015

Messages

392

Office Version

1. 2021

Platform

1. Windows

• Nov 30, 2015

• #4

I am still confused.
This is my understanding. Please see if wronged.

You wanna pick up some items using drop-down lists in cells A2, B2, C2.

The drop-down list may be for A2 is:

categoryA　(group name)
Fruit
apple
kiwi
orange

for B2 is :

Vegetables　(group name)
Celery
Carrot

for C2 is :

Nutts　(group name)
Almonds
Peanuts
Hazelnuts


So after picking up these 3 items as ingredients, you wanna show the name of the resulting product in D2, e.g. "Liftup", "Booster", "Gymman"?

 

Upvote 0

E

everblazing

Board Regular

Joined

Sep 18, 2015

Messages

156

• Nov 30, 2015

• #5

Yes that is exactly right. that's exactly what i am trying to get to.

 

Upvote 0

S

smallxyz

Active Member

Joined

Jul 27, 2015

Messages

392

Office Version

1. 2021

Platform

1. Windows

• Nov 30, 2015

• #6

But we need to know what ingredients are needed to make these products, e.g. "Liftup", "Booster", "Gymman".

Do you have such a table? If so, how does it look like?

 

Upvote 0

E

everblazing

Board Regular

Joined

Sep 18, 2015

Messages

156

• Nov 30, 2015

• #7

smallxyz said:

But we need to know what ingredients are needed to make these products, e.g. "Liftup", "Booster", "Gymman".

Do you have such a table? If so, how does it look like?

Click to expand...

Gymman is combination of Almonds, Hazelnuts, Carrots
Booster is a combination of apple,kiwi,carrot,hazelnuts
Liftup is combination of Orange,Peanut,Celery

 

Upvote 0

S

smallxyz

Active Member

Joined

Jul 27, 2015

Messages

392

Office Version

1. 2021

Platform

1. Windows

• Nov 30, 2015

• #8

Is that exactly 3 ingredients to make a product? (Since given only A2,B2,C2 are for drop-down lists)

I saw 4 in the Booster. (Booster is a combination of apple,kiwi,carrot,hazelnuts)

 

Upvote 0

E

everblazing

Board Regular

Joined

Sep 18, 2015

Messages

156

• Nov 30, 2015

• #9

yes Booster is the one that gives possibility of two selection. however we can have the apple, Kiwi as Applekiwi. wither way the main thing is to know how to get D2 show the name of the combination.

thanks

 

Upvote 0

S

smallxyz

Active Member

Joined

Jul 27, 2015

Messages

392

Office Version

1. 2021

Platform

1. Windows

• Nov 30, 2015

• #10

Hi, as there is insufficient information, I would try to simulate another situation as below. But the rationale of the formula should be the same. Amend to suit your need.

Excel 2007

	A	B	C	D	E	F	G	H	I	J
1	DP_list_1	DP_list_2	DP_list_3	Product			Ingredient A	Ingredient B	Ingredient C	Result Product
2	A1	B1	C1	P1			A1	B1	C1	P1
3	A3	B2	C2	PP3			A2	B2	C2	P2
4	A2	B3	C1	PP2			A3	B3	C3	P3
5	A3	B3	C3	P3			A1	B2	C3	PP1
6							A2	B3	C1	PP2
7							A3	B2	C2	PP3

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula D2 {=INDEX($J$2:$J$7,MATCH(1,($G$2:$G$7=A2)*($H$2:$H$7=B2)*($I$2:$I$7=C2),0))} D3 {=INDEX($J$2:$J$7,MATCH(1,($G$2:$G$7=A3)*($H$2:$H$7=B3)*($I$2:$I$7=C3),0))} D4 {=INDEX($J$2:$J$7,MATCH(1,($G$2:$G$7=A4)*($H$2:$H$7=B4)*($I$2:$I$7=C4),0))} D5 {=INDEX($J$2:$J$7,MATCH(1,($G$2:$G$7=A5)*($H$2:$H$7=B5)*($I$2:$I$7=C5),0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

Last edited: Nov 30, 2015

Upvote 0