Return a Range of Values based on Data Validation

[calvinc123]

All,

I am completely stumped. I would rather not use VBA or Macros for this problem, but understand if that will make things a lot easier. This may be a simple formula, but I have been struggling to figure this one out. Here's the information.

I am trying to auto create a table based on the value selected from a data validation list.

Input: You select from three options in the data validation: Basket 1, 2 or 3. Each of which provide a different allocation of fruit and vegetables.

Output: A table that will provide the exact allocation of the fruit or vegetable WITHOUT returning a "False or 0" for the vegetables or fruits that are NOT included in the basket. Each time you select the different basket the output table regenerates with the right amount of rows based on how many items are in each basket.

Here is the table of data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Kind[/TD]
[TD]Type[/TD]
[TD]Basket 1[/TD]
[TD]Basket 2[/TD]
[TD]Basket 3[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Fruit[/TD]
[TD]50%[/TD]
[TD]0%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Fruit[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]Tomato[/TD]
[TD]Vegetable [/TD]
[TD]0%[/TD]
[TD]10%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD]Fruit[/TD]
[TD]25%[/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]Celery[/TD]
[TD]Vegetable[/TD]
[TD]25%[/TD]
[TD]25%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]Fruit[/TD]
[TD]0%[/TD]
[TD]25%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]Pumpkin[/TD]
[TD]Vegetable[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[/TR]
</tbody>[/TABLE]


After the output page is pulling ONLY information that is NOT equal to zero I am then planning on doing two pie charts one to show the allocation of each Kind & the other to show Type in the basket that is selected in the Input page dropdown.

Hopefully this makes some sense. Let me know if you have any questions!

C

 

[SpreedsheetCrusader]

I find it difficult to do without VBA for the reason that you would like to have the right number of rows generated for the Basket selected. that means that the Fruit that has 0 allocation in the basket should not be displayed entirely.

if you do not mind having that fruit listed with null value or blank, then that is do-able without VBA i guess:

e.g. Basket 1 Selected:

Apple 50%
Orange
Tomato
Cherry 25%
Celery 25%
Grape
Pumpkin

update: i think it is doable without VBA but needs some fine logic and several helper columns i will give it a try.

 

[calvinc123]

Sounds great! Thanks for your help!

 

[SpreedsheetCrusader]

Done it picture below i will explain afterwards in a post:

 

[SpreedsheetCrusader]

here is the code, i set everything in a single page but you can analyze the code and re-arrange the layout as you wish if you understand the formulas. it is easy:

Excel 2010
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3
4		Kind	Type	Basket 1	Basket 2	Basket 3		List	list val	<> 0 ?	index			order	type	value
5		Apple	Fruit	50%	0%	10%		Basket 1	2	0				Orange	Fruit	20%
6		Orange	Fruit	0%	20%	20%		Basket 2		1	1			Tomato	Vegetable	10%
7		Tomato	Vegetable	0%	10%	10%		Basket 3		1	2			Cherry	Fruit	20%
8		Cherry	Fruit	25%	20%	20%				1	3			Celery	Vegetable	25%
9		Celery	Vegetable	25%	25%	20%				1	4			Grape	Fruit	25%
10		Grape	Fruit	0%	25%	0%				1	5
11		Pumpkin	Vegetable	0%	0%	20%				0
12
13
14
15
16
17
Sheet1

Cell Formulas
Range	Formula
M5	=--(CHOOSE($L$5,G5,H5,I5)>0)
M6	=--(CHOOSE($L$5,G6,H6,I6)>0)
M7	=--(CHOOSE($L$5,G7,H7,I7)>0)
M8	=--(CHOOSE($L$5,G8,H8,I8)>0)
M9	=--(CHOOSE($L$5,G9,H9,I9)>0)
M10	=--(CHOOSE($L$5,G10,H10,I10)>0)
M11	=--(CHOOSE($L$5,G11,H11,I11)>0)
N5	=IF(M5=1,COUNTIF($M$5:M5,1),"")
N6	=IF(M6=1,COUNTIF($M$5:M6,1),"")
N7	=IF(M7=1,COUNTIF($M$5:M7,1),"")
N8	=IF(M8=1,COUNTIF($M$5:M8,1),"")
N9	=IF(M9=1,COUNTIF($M$5:M9,1),"")
N10	=IF(M10=1,COUNTIF($M$5:M10,1),"")
N11	=IF(M11=1,COUNTIF($M$5:M11,1),"")
Q5	=IFERROR(INDEX($E$5:$E$11,MATCH(ROWS($N$5:N5),$N$5:$N$11,0)),"")
Q6	=IFERROR(INDEX($E$5:$E$11,MATCH(ROWS($N$5:N6),$N$5:$N$11,0)),"")
Q7	=IFERROR(INDEX($E$5:$E$11,MATCH(ROWS($N$5:N7),$N$5:$N$11,0)),"")
Q8	=IFERROR(INDEX($E$5:$E$11,MATCH(ROWS($N$5:N8),$N$5:$N$11,0)),"")
Q9	=IFERROR(INDEX($E$5:$E$11,MATCH(ROWS($N$5:N9),$N$5:$N$11,0)),"")
Q10	=IFERROR(INDEX($E$5:$E$11,MATCH(ROWS($N$5:N10),$N$5:$N$11,0)),"")
Q11	=IFERROR(INDEX($E$5:$E$11,MATCH(ROWS($N$5:N11),$N$5:$N$11,0)),"")
R5	=IFERROR(INDEX($E$5:$F$11,MATCH(Q5,$E$5:$E$11,0),2),"")
R6	=IFERROR(INDEX($E$5:$F$11,MATCH(Q6,$E$5:$E$11,0),2),"")
R7	=IFERROR(INDEX($E$5:$F$11,MATCH(Q7,$E$5:$E$11,0),2),"")
R8	=IFERROR(INDEX($E$5:$F$11,MATCH(Q8,$E$5:$E$11,0),2),"")
R9	=IFERROR(INDEX($E$5:$F$11,MATCH(Q9,$E$5:$E$11,0),2),"")
R10	=IFERROR(INDEX($E$5:$F$11,MATCH(Q10,$E$5:$E$11,0),2),"")
R11	=IFERROR(INDEX($E$5:$F$11,MATCH(Q11,$E$5:$E$11,0),2),"")
S5	=IFERROR(INDEX($E$5:$I$11,MATCH(Q5,$E$5:$E$11,0),$L$5+2),"")
S6	=IFERROR(INDEX($E$5:$I$11,MATCH(Q6,$E$5:$E$11,0),$L$5+2),"")
S7	=IFERROR(INDEX($E$5:$I$11,MATCH(Q7,$E$5:$E$11,0),$L$5+2),"")
S8	=IFERROR(INDEX($E$5:$I$11,MATCH(Q8,$E$5:$E$11,0),$L$5+2),"")
S9	=IFERROR(INDEX($E$5:$I$11,MATCH(Q9,$E$5:$E$11,0),$L$5+2),"")
S10	=IFERROR(INDEX($E$5:$I$11,MATCH(Q10,$E$5:$E$11,0),$L$5+2),"")
S11	=IFERROR(INDEX($E$5:$I$11,MATCH(Q11,$E$5:$E$11,0),$L$5+2),"")

 

[SpreedsheetCrusader]

now the explanation:

- column K i listed all the baskets names that i have
- column L has a cell linked with a combo box that let the user select the basket
- column M relates to the value resulted based on the user selection at cell L5 if the user select basket 3 then i will go the percentage value at the same row for that particular basket.. if it is bigger than 0 then give me 1 otherwise display 0

- Column N is to index the things in the basket selected, if the adjacent cell at Column M is = 1 then count the range up to that cell
- Column Q starts by counting the Rows up to the adjacent cell at column N then find that count result in the same column (column N) then index that at column E
- Column R and S are straight forward INDEX MATCH nesting to figure the type of the item and the relevant percentage based on the item and the basket selected


my advice is to reconstruct the worksheet as given in the above post and go through the formula you will find that it is easy and not difficult as it looks.

 

[calvinc123]

Thank you so much! This will be great. I will go through it in finer detail. I may be calling upon you for further excel projects haha

 

[SpreedsheetCrusader]

Hi bro. Enjoy.

As I mentioned go through it carefully and you will understand it, it is very straight forward.

If you have any inquiries about the formula post it here and I will try to explain.

May the force be with you