Drop down menus

**Steel_lady** · 02-05-2008, 05:06 AM

I did a drop down menu like we were talking here before and when I added a lot of things on the menu, now when I click on the cell to have the election, I don't get anything. It jusd doesn't want to open. Is it because of too much information? I really need so much information on the menu. How to solve it? Thanx!

**royUK** · 02-05-2008, 05:33 AM

You need to give more information, a link to any previous posts would be a start.

**Steel_lady** · 02-05-2008, 05:44 AM

I used this:

http://www.contextures.com/xlDataVal01.html

**Steel_lady** · 02-05-2008, 06:30 AM

It looks like it accepts only 111 elements on the list?!!! (When I put mor, it simply doesn't display them) Can this be changed?

**royUK** · 02-05-2008, 07:03 AM

You are using Data Validation. I am not aware of any limitations to the length of a list if i is contained in a Range of Cells. If it is added as a comma separated list there may be a limitation of 255 characters.

How are you setting up the list?

**Steel_lady** · 02-05-2008, 07:45 AM

no it is not coma separated list it is the range of cells that is supposed to be applied to the whole column having so many enteries as the data in the column. That's why I am confused

**EdMac** · 02-05-2008, 07:54 AM

Hi,

Have you checked that your named list includes all the range you want it to include?

I have just set up a list with 120 items with no problems (Using 2003)

Ed

**royUK** · 02-05-2008, 08:29 AM

Originally Posted by Steel_lady

no it is not coma separated list it is the range of cells that is supposed to be applied to the whole column having so many enteries as the data in the column. That's why I am confused

The best way to do this is with a Dynamicc Named Range that expands to include all the data as it is added, not refer to the whole column.

Can you attach the workbook?

**Steel_lady** · 02-05-2008, 07:19 PM

I can not attach the workbook now. I did it like in this example:

Create a Dynamic Range

You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.

Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the name in the Name Box, to select the range on the worksheet.

1. Choose Insert>Name>Define
2. Type a name for the range, e.g. NameList

3. In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in the column, e.g.:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
In this example, the list is on Sheet1, starting in cell A1
The arguments used in this Offset function are:
1. Reference cell: Sheet1!$A$1
2. Rows to offset: 0
3. Columns to offset: 0
4. Number of Rows: COUNTA(Sheet1!$A:$A)
5. Number of Columns: 1
Note: for a dynamic number of columns, replace the 1 with:
COUNTA(Sheet1!$1:$1)
4. Click OK

**EdMac** · 02-06-2008, 04:50 AM

OK,

So where is the list data (cell refs) and what is the formula you typed?

Ed

**Steel_lady** · 02-06-2008, 08:23 AM

offset(ref!$c$3;0;0;counta(Ref!$c:$c);1)

So it works perfectly untill I fill it over that data cuantity. I thought it is because of character number in some of the cells but when I change the data it happens again.