Removing "" from the Dropdown

[Xiggie]

Hi!

I am trying to create a dropdown from H23:H27.

The plan was to exclude values next to 0 from the dropdown, and it worked, to an extent - but now I cannot create a condition to adjust the length of the list to not include "" cells.

My dropdown now looks like this, as it considers my formula a value.

Any suggestions would be welcome!!

Mercenaries Ledger.xlsm
	B		D	E	F	G	H
21			Group 1				1
22
23	1		Value 1	55,555.56 ₽			Value 1
24	1		Value 2	55,555.56 ₽			Value 2
25	1		Value 4	55,555.56 ₽			Value 4
26	1		Value 3	55,555.56 ₽			Value 3
27	0		Value 1	55,555.56 ₽
Loot

Cell Formulas
Range	Formula
B23:B26	B23	=IF(D23="Наемник", 0, 1)
B27	B27	=IF(D27="Value 1", 0, 1)
E23	E23	='Shared Expenses'!$J$62
E24	E24	='Shared Expenses'!$K$62
E25	E25	='Shared Expenses'!$L$62
E26	E26	='Shared Expenses'!$M$62
E27	E27	='Shared Expenses'!$N$62
H23:H27	H23	=IFERROR(INDEX(D$23:D$27,SMALL(IF(($B$23:$B$27=$H$21),ROW($B$23:$B$27)-ROW($B$23)+1),ROWS(H$23:H23))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D27	Expression	=AND(NOT(ISBLANK(C$4)),ISBLANK(D$4))	text	NO
D26	Expression	=AND(NOT(ISBLANK(C$4)),ISBLANK(D$4))	text	NO
D25	Expression	=AND(NOT(ISBLANK(C$4)),ISBLANK(D$4))	text	NO
D24	Expression	=AND(NOT(ISBLANK(C$4)),ISBLANK(D$4))	text	NO
D23	Expression	=AND(NOT(ISBLANK(C$4)),ISBLANK(D$4))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D23:D27	List	=Mercenaries!$A$2:$A$1048576

 

[6StringJazzer]

Your list formula is

=Mercenaries!$A$2:$A$1048576

and yet you do not show us what is in that column. Also, specifying over a million rows for a list is usually not a good idea.

Lacking the necessary information I would guess this might work for your list formula. This will include only non-blank cells regardless of whether they contain a formula:

=OFFSET(Mercenaries!$A$2,0,0,COUNTIF(Mercenaries!$A:$A,"*?"))

 

[Xiggie]

Hello and thank you for the reply!

I shall try that!

For context, I am working on a calculator for a game to calculate and distribute the income between guild members.
Mercenaries is a list of participants. Now, D23:D27 is a group (a group is always 5 members). The values in the group (D23:D27) are picked based on group participants (5 participants from the list).

I am trying to make a new drop down, which would only include the participants of the group.

 

[Xiggie]

Don't see how I can edit my previous post, but I am looking for a way to work with H23:H27 without touching the original range $A$2:$A$1048576
I need a dropdown for a different table:

Mercenaries Ledger.xlsm
	F
7	Ing1more
8	3imA
9	3imA
10	Komar174
11	Serg710
Shared Expenses

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F44:F56,F13:F19,F7:F11	Expression	=AND(NOT(ISBLANK($B7)),ISBLANK($F7))	text	NO
F44:F56,F13:F19,F7:F11	Expression	=AND(ISERROR(MATCH(F7,$J$5:$AM$5,0)),NOT(ISBLANK(B7)))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F7:F11	List	=Loot!$H$23:$H$27

 

[wolf735]

this is hopfully the same idea as you. if so hopes the helps.

Book25667.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Merc group	name	Rank	payment	Number	Column1	Column6	Column1		helper
2	Group 1	Zubin Varla			1	1	1	Group 1		Group 1
3	Group 1	Will Attenborough			2	1	1			Group 2
4	Group 1	Tamer Burjaq			3	1	1			Group 3
5	Group 1	Steve Toussaint			4	1	1			Group 4
6	Group 1	Simon Lennon			5	1	1			Group 5
7	Group 2	Shalom Brune-Franklin			6	2	#N/A			Group 6
8	Group 2	Sean Ward			7	2	#N/A			Group 7
9	Group 2	Sean Sagar			8	2	#N/A			Group 8
10	Group 2	Rolan Bell			9	2	#N/A			Group 9
11	Group 2	Pranesh Maharaj			10	2	#N/A			Group 10
12	Group 3	Patrick Sithole			11	3	#N/A
13	Group 3	Patrick McNamee			12	3	#N/A
14	Group 3	Olly Rix			13	3	#N/A
15	Group 3	Nico Mirallegro			14	3	#N/A
16	Group 3	Nick Preston			15	3	#N/A
17	Group 4	Nebras Jamali			16	4	#N/A
18	Group 4	Nabil Elouahabi			17	4	#N/A
19	Group 4	Michelle Keegan			18	4	#N/A
20	Group 4	Matthew McNulty			19	4	#N/A
21	Group 4	Mark Armstrong			20	4	#N/A
22	Group 5	Luke Pasqualino			21	5	#N/A
23	Group 5	Lawrence Walker			22	5	#N/A
24	Group 5	Lacey Turner			23	5	#N/A
25	Group 5	Kirsty Averton			24	5	#N/A
26	Group 5	Kaine Zajaz			25	5	#N/A
27	Group 6	Josh Bowman			26	6	#N/A
28	Group 6	Jonas Khan			27	6	#N/A
29	Group 6	John Michie			28	6	#N/A
30	Group 6	Jed O'Hagan			29	6	#N/A
31	Group 6	Jack Parry Jones			30	6	#N/A
32	Group 7	Iwan Rheon			31	7	#N/A
33	Group 7	Houston Skinner			32	7	#N/A
34	Group 7	Harki Bhambra			33	7	#N/A
35	Group 7	Hamza Jeetooa			34	7	#N/A
36	Group 7	Fiona Skinner			35	7	#N/A
37	Group 8	Dwane Walcott			36	8	#N/A
38	Group 8	Dominic Jephcott			37	8	#N/A
39	Group 8	Danny Hatchard			38	8	#N/A
40	Group 8	Charley Palmer Rothwell			39	8	#N/A
41	Group 8	Ben Batt			40	8	#N/A
42	Group 9	Ben Aldridge			41	9	#N/A
43	Group 9	Ashley Houston			42	9	#N/A
44	Group 9	Arinze Kene			43	9	#N/A
45	Group 9	Anthony Oseyemi			44	9	#N/A
46	Group 9	Amy-Leigh Hickman			45	9	#N/A
47	Group 10	Ade Oyefeso			46	10	#N/A
48	Group 10	Adam Astill			47	10	#N/A
Sheet1

Cell Formulas
Range	Formula
E2:E48	E2	=ROW(B2)-1
F2:F48	F2	=INT(([@Number]-1)/5+1)
G2:G48	G2	=MATCH([@[Merc group]],Table48[Column1],0)
A2:A48	A2	=TEXTJOIN(" ",TRUE,"Group",[@Column1])
J2:J48	J2	=IFERROR(INDEX(Table3044[Merc group],MATCH(0,INDEX(COUNTIF($J$1:J1,Table3044[Merc group]),),0)),"")

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=OFFSET($J$2,,,COUNTIF($J$2:$J$48,"?*"))

 

[wolf735]

colum e2 and f2 where to help me the 5 as proof.