Removed Blanks in Data Validation - Office 365

[smitpau]

Hi,

As the title says I'd like to remove blanks in a data validation list, I've followed the article below but the way Office 365 works is after effectively changing the formula below it expands out the cell for the full list, this means when I reference the cell in name manager the cells are constantly changing instead of being in a single cell.

Does anybody have any ideas how to stop the list spilling down into the cells below as I believe it should then work.

Thanks for reading!

=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT(--(Sheet1!$C$2:$C$1000<>"")))

https://www.myonlinetraininghub.com/excel-ignore-blanks-in-data-validation-list

 

[jasonb75]

I didn't read the whole article but it looks like a complicated way to perform a simple task.
This assumes no header in C1, if there is then the validation formula will need changing to =OFFSET($C$2,,,ROWS($C:$C)-COUNTIF($C:$C,"")-1)

Book1 (version 1).xlsb
	A	B	C	D	E
1
2	Chamomile		Chamomile		Chamomile
3	Lavender		Lavender
4			Liquorice
5	Liquorice		Chai
6			Ginger
7	Chai		Oolong
8	Ginger
9	Oolong
Sheet3

Cell Formulas
Range	Formula
C2:C9	C2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$9)/SIGN(LEN(A$2:A$9)),ROWS(C$2:C2))),"")

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=OFFSET($C$2,,,ROWS($C:$C)-COUNTIF($C:$C,""))

 

[Peter_SSs]

I believe that you have the FILTER function so isn't this what you want? (If not please give us a small set of sample dummy data with XL2BB & explain in relation to that)

smitpau 2020-05-19 1.xlsm
	A	B	C	D	E
1	Full Data		No Blanks		Data Validation Cell(s)
2	Chamomile		Chamomile		Chai
3	Lavender		Lavender		Oolong
4			Liquorice
5	Liquorice		Chai
6			Ginger
7	Chai		Oolong
8	Ginger
9	Oolong
10
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=FILTER(A2:A1000,A2:A1000<>"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E2:E3	List	=C$2#

 

[smitpau]

Thanks very much both, much appreciated.

It's that # at the end of the data validation that's done the trick.

 

[Peter_SSs]

Thanks very much both, much appreciated.

It's that # at the end of the data validation that's done the trick.

Cheers. (It's the same as it was in your other thread here )

 

[smitpau]

Haha fair point I mentally ignored that as thought it wouldn't help given those rows were fixed as there was only ever going to be 3 of them, but see it doesn't matter now it basically takes a dynamic array of x length into a single cell which is useful.

 

[Peter_SSs]

Yes the # indicates that the cell reference is the first cell in a 'spill range' and Excel then knows to use the full spill range no matter how many rows it contains.