Data Validation with a list that varies in size

  • Thread starter Thread starter SKelly
  • Start date Start date
S

SKelly

I have created a template spreadsheet for a non-technical user
On one tab, I have a comparison between Golfer A and Golfer B.
On the front tab, there is a list of all the golfers for a particular
weekend.
I left 24 slots open. However, there is no requirement that all 24
are used.
How do I eliminate blank rows from my dropdown list if that dropdown
is
filled with a Named Range and that range is defined as a column of 24
(potential) name cells.

For example, if I have a Range of 24 cells A2:A25 (A1 = "Name")
defined as GolferList, but only A2:A11 are filled, I want my dropdown
list to have 10 names on it and NOT 10 names + 14 blank lines. Can
any one help?

Sean Kelly
Scotts Valley
 
Take a look at Debra Dalgleish's dynamic data source tip. Holds good for
what you want just as mach as a Pivot Table.

Use a Dynamic Data Source
http://www.contextures.com/xlPivot01.html

Define your named formula using Debra's formula

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7)

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),1) - Note the 1

(Change the 7 to a 1) then do Data / Validation / list and type in =formula
replacing 'formula' with your defined name, not forgetting the = sign.

Regards
Ken...................
 
Hi,

Choose Insert, Name, Define and enter the name GolferList in the Names in
workbook box and then in the Refers to box enter the formula

=OFFSET(Sheet1!$2$2,0,0,COUNTA(Sheet1!$A$2:$A$25))

Use the name =GolferList in the Data, Validation, List, Source box
 
Back
Top