Reputation: 3
To clarify I have a list that grows every few hours in column A:
A
Carrots
Peas
Turnips
Potatoes
Peas
Cabbage
Celery
Spinach
Carrots
Chives
I would like to have a drop down list that at any given time will have the bottom five entries. So right now it would have:
Cabbage
Celery
Spinach
Carrots
Chives
Any help would be appreciated!
Upvotes: 0
Views: 30
Reputation: 84465
Say your data started in A2 then create a named range that has the formula
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-5)
Then have the drop down list populated by the named range. Say your named range was "Veg" then you might use
Data validation >
Allow: List
Source: = Veg
which would show only the last 5 items as valid selections for a cell.
Note that i have used {} only because i am showing the formula used in a cell rather than added via the name manager to refer to a dynamic range. You would press Ctrl + F3 to add the formula as first shown into the name manager when creating your named range.
Upvotes: 1