Limiting a Drop Down List (Conditional Formatting)

[Aussie Chris]

Hi,

I have a table (simplified below) where I am tracking shipping containers.

Id like to create a drop down list (using conditional formatting) of container numbers where the status is not set to DELIVERED on a separate sheet. The idea being that I can create an auto filling container weight declaration from the data in the appropriate rows of the table. The biggest problem I have is that the table currently has over 200 records which makes scrolling the drop down list very tedious. What I'd like to do is exclude any containers from that drop down list where the status is set to DELIVERED. This would reduce the size of the drop down list by more than 90% making it a little more manageable.

The only solution I have come up with thus far is to base the drop down list off a query that filters out the DELIVERED containers but this requires the need to refresh query each time new records are added to the table.

Is it possible to simplify this to remove the need for a query and without resorting to VB?

Container Number	Status
AA11	DELIVERED
BB22	DELIVERED
CC33	DELIVERED
DD44	LOADED
EE55	EMPTY
FF66	LOADED
GG77	EMPTY

<tbody>
</tbody>

 

[FDibbins]

Hi, welcome to the board

(I think you may have some terms mixed up there, Conditional Formatting is only used to change the format - color/font/etc) of a cell)

Anyway, I think 1 way around this would be to use a helper column...

	A​	B​	C​
1​	Container Number	Status	Helper
2​	AA11	DELIVERED	0​
3​	BB22	DELIVERED	0​
4​	CC33	DELIVERED	0​
5​	DD44	LOADED	1​
6​	EE55	EMPTY	2​
7​	FF66	LOADED	3​
8​	GG77	EMPTY	4​

C2=COUNTIF($B$2:B2,"<>delivered")
copied down

Then to do the extract...

	F​	G​
1​	Container Number	Status
2​	DD44	LOADED
3​	EE55	EMPTY
4​	FF66	LOADED
5​	GG77	EMPTY

F2=IFERROR(INDEX(A:A,MATCH(ROW(1:1),$C:$C,0)),"")
copied down and across as needed

 

[Aussie Chris]

Sorry, you're right. I've mixed up my conditional formatting with my data validation.

 

[mikerickson]

You can do this without helper column. Put the CSE formula in D1 and drag down

Unknown
	A	B	C	D
1	Container Number	Status		DD44
2	AA11	DELIVERED		EE55
3	BB22	DELIVERED		FF66
4	CC33	DELIVERED		GG77
5	DD44	LOADED
6	EE55	EMPTY
7	FF66	LOADED
8	GG77	EMPTY
9
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
D1	{=INDEX(A:A, SMALL(IF($B$2:$B$100<>"delivered",ROW($B$2:$B$100),999),ROW(A1)), 1)&""}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aussie Chris]

Thanks... I can work with that