Data Validation to not show blanks at the end

[Godwin117]

I have below formula, when I click ok after entering the formula into that data validation source, it shows the formula as the options and not the values that should be there. Not sure if anyone else has had an issue like this. Any help would be greatly appreciated.

=OFFSET(Data Validation!$K$2,,,COUNTA(Data Validation!$K:$K)-1)

 

[Godwin117]

That's it. Formulas will be counted in the CountA function

We need to use match and change the result of your formula so that it returns a zero instead of blank. If the last few formulas now result in a "", then change it so it results in 0.

This returns the the last text value in the column
=OFFSET('Data Validation'!$K$1,1,0,match("zzzzzzzzzzz",'Data Validation'!$K:$K)-1,1)

Thank you, that worked.

 

[Peter_SSs]

Since you have Excel 365 then if yours has the FILTER function there is a considerably simpler way.

In the Data Validation sheet enter the function as shown in L2. The other results will automatically 'spill' to the following rows as required.

Godwin117 2020-04-21 1.xlsm
	K	L
1
2		ABC
3	ABC	DEF
4		GHI
5	DEF	JKL
6	GHI	MNO
7	JKL
8
9
10	MNO
11
Data validation

Cell Formulas
Range	Formula
L2:L6	L2	=FILTER(K2:K50,K2:K50<>"")
Dynamic array formulas.

In your Data validation cell(s) in the other worksheet you simply need to point the DV at the top cell in the new DV list we just created & add the # at the end as shown below.

Godwin117 2020-04-21 1.xlsm
	A
1
2
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A2	List	='Data validation'!$L$2#