StumpedatBest
StumpedatBest

Reputation: 3

I'm trying to create a formula in excel to select the bottom 5 values

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

Answers (1)

QHarr
QHarr

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.

Data

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

Related Questions