Settings of Data Validation

[BJ]

Hi,

I have couple of questions regarding how to use 'Data Validation' in Excel.

1. when use a list to show the data source for a cell in an Excel file, the
maximum items that showing from the list when user clicks on it is 8 items,
is that possible to expand it to show more? for example, when user clicks on
the arrow, the 26 periods can be showed.

2. instead of showing from the first one from the source range, is that
possible to choose which one to show on the top?

thank you.

 

[T. Valko]

1. You can use a combo box for this. With a combo box you can set how many
items are displayed. However, a combo box works differently than a data
validation drop down list.

Saved from an old post (and the screencap links still work!):

Combo box explanation:

See this screencap:

http://img126.imageshack.us/img126/4921/cboxkh8.jpg

A combo box from the Forms toolbar is different from a data validation drop
down list in that a data validation drop down list places the selection you
make into the cell that holds the drop down. A combo box doesn't do that. A
combo box will place the *number* of the item selected in a *linked cell*.
Then, if you need to refer to the selection made you need a formula that
will return the item selected based on the item number of the linked cell.

In the screencap I've drawn the combo box on top of cells D2 and E2. It's
important to know that a combo box is an *object* and an object does not
reside in a cell. An object "sits" on top of the worksheet. This means we
can use cells D2 and E2 and because our combo box sits on top of those cells
whatever we put in cells D2 and E2 will be hidden from view.

So, we're going to use cell D2 as the linked cell and we'll use cell E2 for
the formula that will return the item selected from the list.

Assume you've drawn a combo box over cells D2 and E2 as in the screencap.
Select the combo box and right click. A menu will appear.

Select Format Control>Control tab

http://img514.imageshack.us/img514/3459/cbox2qs8.jpg

The Input range is the source of the list - $A$1:$A$10
The linked cell is $D$2

When you make a selection from the list the item *number* is returned to the
linked cell. In the screencap the selection is "green" and it's the 3rd item
in the list. The value returned to the linked cell is 3. To return the
actual item we need a formula. I've put that formula in cell E2:

=INDEX(A1:A10,D2)

The formula in E2 returns green. So, to refer to the item selected from the
combo box you need to refer to cell E2:

=E2

Returns: green


2. No. The order or the list is determined by the order of the source.

 

[KC hotmail com>]

1) When I used Data Validation for a drop-down list, I usually set my list up
on another worksheet, give it a defined name (usually a dynamic named range
or using Excel's List functionality), and point to that Name as the Source.
I have lists with way more than 8 entries and the drop-down box always works
just fine to show all available selections.

2) The order is set by the Source. When you do a list on another sheet as
discussed above, you are in total control of the order of the list entries.

 

[BJ]

since i am showing the 52 periods(Sundays) of the 2 years range, i have to
put them in order, for example, 2008 first then 2009. but user wants to show
the 1/4/2009 first which is the first Sunday of 2009 and is the middle item
of the whole list.

 

[KC hotmail com>]

If you have a place where the user can indicate the desired start date (say
in E1). You already have a master list of Sunday dates (let's call it
MasterList), but you don't want to use that for your data validation list, so
set up another list (let's put in J1 with no header title) where the first
entry (J1) is:
=VLOOKUP(E1,MasterList,1)

This will pull up the first date in your MasterList which is not past the
desired date (so if he enters 1/1/09, he'll get the Sunday in your list which
is on or before 1/1/09, or 12/28/08).

From there, just add the following formula down that column (J2 on down)
=IF(OR(ISBLANK(INDEX(MasterList,MATCH(J1,MasterList)+1)),ISERROR(INDEX(MasterList,MATCH(J1,MasterList)+1))),"",INDEX(MasterList,MATCH(J1,MasterList)+1))

Finally, you can just use a dynamic named range to limit the new list to all
the non-blank entries. Go to Insert > Name > Define, create DVList and give
it the following reference:
=OFFSET(Sheet1!$J$1,0,0,COUNT(Sheet1!$J$1:$J$100))

Now you're data validation will begin with a more relevant date according to
the user's input and will not have a bunch of confusing, empty entries at the
end.