10 lists to select from only populated data

[Tsinja]

I have a problem that I don't know if its possible or not. so im a big Gamer and trying to see if i can build a spreadsheet to see the best things to equip.
so my values that i want to populate my spread sheet is from: 10 different lists each list contains
10 rows
5 columns
so based upon 3 criteria(level 1-10, where to put item on body, type of item) that is selected to tell which list to use, row and column.
then retrieve info(not all cells contain data) and populate the spreadsheet for said criteria.

The problem I have using If/And formulas is there are too many in-bedded. Also not sure how to keep using same cells in spread sheet
for the different queries i do.

 

[StephenCrump]

Welcome to the Forum!

Have at look at the INDEX/MATCH construction ...

You'll need your formula to MATCH:

1. The column headers: Cloth Hood, Leather Cap, Steel Helmet ...
2. The row headers 1, 2, 3 ... for the levels.

If your MATCH() on row headers for level 2, say, returns row R, then
- Health for Level 2 will also be on Row R
- Damage for Level 2 will be on Row R+1
- Armor% for Level 2 will be on row R+2
... etc

I recommend you install the XL2BB add-in: XL2BB - Excel Range to BBCode
This will allow you to post screenshots that others can easily copy, i.e. rather than pictures.

 

[Tsinja]

I have never used those before and when looking them up just confused me more.
I have used the XL2BB this time hope it helps

WORLD SEED.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1		LEVEL		Armor		Item																	Levels	Stats	Cloth Hood	Leather Cap	Steel Helmet	Tribal Head Gear	Berards Helmet
2		1		Head		Cloth Hood																	1	Health
3	HEAD																							Damage
4																								Armor%	3.0%	6.0%	10.0%	5.0%	8.0%
5		Health		Damage		Armor %	3.0%	Block Chance %		Dodge Chance %	5.0%	Initiative	5.0	Durability		Crit Chance %		Crit Damage %		Armor Penetration				Block Chance %			-2.0%		1.0%
6	CHEST																							Dodge Chance %	5.0%		-2.0%
7																								Initiative	5.0		-5.0
8		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Durability					10.0
9	HAND																							Crit Chance %
10																								Crit Damage %
11		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Armor Penitration
12	BOOTS																								Cloth Hood 2
13																							2	Health
14		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Damage
15	SHIELDS																							armor%	3.5%	6.5%	10.5%	5.5%	8.5%
16																								Block Chance %			-2.0%		1.0%
17		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Dodge Chance %	5.0%		-2.0%
18	TROPHY																							initiative	5		-5.0
19																								Durability					10.0
20		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Crit Chance %
21	Weopon 1																							Crit Damage %
22																								Armor Penitration
23		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration					Cloth Hood 3
24	Weopon 2																						3	Health
25																								Damage
26		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				armor%	4.0%	7.0%	11.0%	6.0%	9.0%
27		Total Health	0.0%	Total Damage		Total Armor %	3.0%	Total Block Chance		Total Dodge Chance	5.0%	Initiative	5.0	Durability		Total Crit Chance		Total Crit Damage		Total Penitration				Block Chance %			-2.0%		1.0%
28																								Dodge Chance %	5.0%		-2.0%
29																								initiative	5		-5.0
30																								Durability					10
31	1	Head	Chest	Hand	Boots	Shields	Gems	Orbs	Trophy															Crit Chance %
32	2	Bernards Helmet	Chest	Cloth Mittens	Cloth Boots	Aegis	Amarine	Crushing	Berry															Crit Damage %
33	3	Cloth Hood	Cloth Robe	Ingas Gloves	Leather Boots	Bloks Shell	Amethyst	Draining	Bone															Armor Penitration
34	4	Leather Cap	Ishans Curse	Leather Gloves	Steel Greaves	Idolina	Diamond	Freezing	Collar																Cloth Hood 4
35	5	Steel Helmet	Leather Harness	Steel Gauntlets	Tribal Stompers	Moon Shield	Emerald	Scorching	Deadwood														4	Health
36	6	Tribal Headgear	Steel Curiass	Tribal Wristbands	Wyns Walkers	Shields	Onyx		Frost Scale															Damage
37	7		Tidens Kappa				Opal		Ginsing															armor%	4.5%	7.5%	11.5%	6.5%	9.5%
38	8		Tribal Chestguard				Pearl		Hide															Block Chance %			-2.0%		1.0%
39	9						Ruby		Horn															Dodge Chance %	5.0%		-2.0%
40	10						Saphire		Ice Cube															initiative	5		-5.0
41							Topaz		Jaw															Durability					10
42									Pelt															Crit Chance %
43									Pincer															Crit Damage %
44									Scale															Armor Penitration
45									Shard														5		Cloth Hood 5
46									Skin															Health
47									Skull															Damage
48									Sting															armor%	5.0%	8.0%	12.0%	7.0%	10.0%
49									Sulfur															Block Chance %			-2.0%		1.0%
50									Tail															Dodge Chance %	5.0%		-2.0%
51									Talon															initiative	5		-5.0
52									Thorn															Durability					10.0%
53									Tongue															Crit Chance %
54									Tusk															Crit Damage %
55									Twig															Armor Penitration
56									Void Globe														6		Cloth Hood 6
World Seed

Cell Formulas
Range	Formula
E5	E5	=IF(AND(B2=1,D2="Head",F2="Cloth Hood"),IF(Y3=0,"",Y3))
G5	G5	=IF(AND(B2=1,D2="HEAD",F2="Cloth Hood"),IF(Y4=0,"",Y4))
I5	I5	=IF(AND(B2=1,D2="HEAD",F2="Cloth Hood"),IF(Y5=0,"",Y5))
K5	K5	=IF(AND(B2=1,D2="HEAD",F2="Cloth Hood"),IF(Y6=0,"",Y6))
M5	M5	=IF(AND(B2=1,D2="HEAD",F2="Cloth Hood"),IF(Y7=0,"",Y7))
O5	O5	=IF(AND(B2=1,D2="HEAD",F2="Cloth Hood"),IF(Y8=0,"",Y8))
Q5	Q5	=IF(AND(B2=1,D2="HEAD",F2="Cloth Hood"),IF(Y9=0,"",Y9))
S5	S5	=IF(AND(B2=1,D2="HEAD",F2="Cloth Hood"),IF(Y10=0,"",Y10))
U5	U5	=IF(AND(B2=1,D2="HEAD",F2="Cloth Hood"),IF(Y11=0,"",Y11))
O17	O17	=IF(H1=1,#REF!,IF(H1=2,#REF!,IF(H1=3,#REF!,IF(H1=4,#REF!,IF(H1=5,#REF!,IF(H1=6,#REF!,IF(H1=7,#REF!,IF(H1=8,#REF!,IF(H1=9,#REF!,IF(H1=10,#REF!, ""))))))))))
C27	C27	=SUM(C8,C11,C14,C17,C20,C23,C26)
G27,K27,I27	G27	=IF(SUM(G8,G11,G14,G5,G20,G23,G26)=0,"",SUM(G8,G11,G14,G5,G20,G23,G26))
M27,O27	M27	=IF(SUM(M5,M8,M11,M14,M17,M20,M23,M26)=0,"",SUM(M5,M8,M11,M14,M17,M20,M23,M26))
Q27,U27,S27	Q27	=IF(SUM(Q8,Q11,Q14,Q17,Q20,Q23,Q26)=0,"",SUM(Q8,Q11,Q14,Q17,Q20,Q23,Q26))

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=$A$31:$A$40
D2	List	=$B$31:$I$31
F2	List	=$B$32:$B$40

 

[StephenCrump]

Here's one way you could do this. I have used helper cells for the row and column match, but these could be rolled into one formula.

The approach can easily be extended to include other tables, i.e. for Chest, Hand, Boots, ...

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1		LEVEL		Armor		Item		MyRow	MyCol														Levels	Stats	Cloth Hood	Leather Cap	Steel Helmet	Tribal Head Gear	Berards Helmet
2		1		Head		Cloth Hood		2	3														1	Health
3	HEAD																							Damage
4																								Armor%	0.03	0.06	0.1	0.05	0.08
5		Health		Damage		Armor %	0.03	Block Chance %		Dodge Chance %	0.05	Initiative	5	Durability		Crit Chance %		Crit Damage %		Armor Penetration				Block Chance %			-0.02		0.01
6	CHEST																							Dodge Chance %	0.05		-0.02
7																								Initiative	5		-5
8		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Durability					10
9	HAND																							Crit Chance %
10																								Crit Damage %
11		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Armor Penitration
12	BOOTS																								Cloth Hood 2
13																							2	Health
14		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Damage
15	SHIELDS																							armor%	0.035	0.065	0.105	0.055	0.085
16																								Block Chance %			-0.02		0.01
17		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Dodge Chance %	0.05		-0.02
18	TROPHY																							initiative	5		-5
19																								Durability					10
20		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				Crit Chance %
21	Weopon 1																							Crit Damage %
22																								Armor Penitration
23		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration					Cloth Hood 3
24	Weopon 2																						3	Health
25																								Damage
26		Health		Damage		Armor %		Block Chance %		Dodge Chance %		Initiative		Durability		Crit Chance %		Crit Damage %		Armor Penetration				armor%	0.04	0.07	0.11	0.06	0.09
27		Total Health		Total Damage		Total Armor %		Total Block Chance		Total Dodge Chance		Initiative		Durability		Total Crit Chance		Total Crit Damage		Total Penitration				Block Chance %			-0.02		0.01
28																								Dodge Chance %	0.05		-0.02
29																								initiative	5		-5
30																								Durability					10
31	1	Head	Chest	Hand	Boots	Shields	Gems	Orbs	Trophy															Crit Chance %
32	2	Bernards Helmet	Chest	Cloth Mittens	Cloth Boots	Aegis	Amarine	Crushing	Berry															Crit Damage %
33	3	Cloth Hood	Cloth Robe	Ingas Gloves	Leather Boots	Bloks Shell	Amethyst	Draining	Bone															Armor Penitration
34	4	Leather Cap	Ishans Curse	Leather Gloves	Steel Greaves	Idolina	Diamond	Freezing	Collar																Cloth Hood 4
35	5	Steel Helmet	Leather Harness	Steel Gauntlets	Tribal Stompers	Moon Shield	Emerald	Scorching	Deadwood														4	Health
36	6	Tribal Headgear	Steel Curiass	Tribal Wristbands	Wyns Walkers	Shields	Onyx		Frost Scale															Damage
37	7		Tidens Kappa				Opal		Ginsing															armor%	0.045	0.075	0.115	0.065	0.095
38	8		Tribal Chestguard				Pearl		Hide															Block Chance %			-0.02		0.01
39	9						Ruby		Horn															Dodge Chance %	0.05		-0.02
40	10						Saphire		Ice Cube															initiative	5		-5
41							Topaz		Jaw															Durability					10
42									Pelt															Crit Chance %
43									Pincer															Crit Damage %
44									Scale															Armor Penitration
45									Shard														5		Cloth Hood 5
46									Skin															Health
47									Skull															Damage
48									Sting															armor%	0.05	0.08	0.12	0.07	0.1
49									Sulfur															Block Chance %			-0.02		0.01
50									Tail															Dodge Chance %	0.05		-0.02
51									Talon															initiative	5		-5
52									Thorn															Durability					0.1
53									Tongue															Crit Chance %
54									Tusk															Crit Damage %
55									Twig															Armor Penitration
56									Void Globe														6		Cloth Hood 6
Sheet1

Cell Formulas
Range	Formula
H2	H2	=MATCH(Level,INDEX(TableHead,,1),)
I2	I2	=MATCH(Item,INDEX(TableHead,1,),)
C5,U5,S5,Q5,O5,M5,K5,I5,G5,E5	C5	=IFERROR(1/(1/INDEX(TableHead,MyRow+COLUMNS($B5:B5)/2,MyCol)),"")

Named Ranges
Name	Refers To	Cells
Item	=Sheet1!$F$2	I2
Level	=Sheet1!$B$2	H2
MyCol	=Sheet1!$I$2	U5, S5, Q5, O5, M5, K5, I5, G5, E5, C5
MyRow	=Sheet1!$H$2	U5, S5, Q5, O5, M5, K5, I5, G5, E5, C5
TableHead	=Sheet1!$W$1:$AC$55	U5, S5, Q5, O5, M5, K5, I5, H2:I2, G5, E5, C5

 

[Tsinja]

THANK YOU SO MUCH FOR THIS. I would not have figured this out.
Looks like I got a lot more to lean in EXCEL.
Thanks Again

 

[StephenCrump]

You're welcome.