Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Settings of Data Validation
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Settings of Data Validation
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 ControlControl 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. -- Biff Microsoft Excel MVP "BJ" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Settings of Data Validation
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. -- Please remember to indicate when the post is answered so others can benefit from it later. "BJ" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Settings of Data Validation
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" wrote: 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. -- Please remember to indicate when the post is answered so others can benefit from it later. "BJ" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Settings of Data Validation
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,MasterLis t)+1)),ISERROR(INDEX(MasterList,MATCH(J1,MasterLis t)+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. -- Please remember to indicate when the post is answered so others can benefit from it later. "BJ" wrote: 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" wrote: 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. -- Please remember to indicate when the post is answered so others can benefit from it later. "BJ" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003. Data/Validation/Settings - Allow/List: sizing list? | Excel Discussion (Misc queries) | |||
Conditional End Date in Data Validation Settings | Excel Worksheet Functions | |||
Printing Validation Settings | Excel Discussion (Misc queries) | |||
'Fill-in ignores Data Validation settings! | Excel Discussion (Misc queries) | |||
DATA/VALIDATION/SETTINGS | Excel Worksheet Functions |