Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing blank in Dynamic range
I have a named dynamic range with the source
=OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J),1) Thia is set up as the data validation list in a cell with a drop down list It is set so that once the drop down is activated there is the option of selecting a blank from the bottom of the list so that if you activate the cell in error you don't have to delete any entry you are forced to make, you can select the blank and leave the cell blank. This works fine until the list gets quite long at which time there is no option of a blank.I don't know the number in the list where this starts but certainly a list over 100 has this problem. On shorter lists the blank is selectable. The selection is correct as when you check the named list it shows the correct range. I would welcome any views on solving this if it can be resolved. Kind regards Graham Haughs Turriff Scotland |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing blank in Dynamic range
Hi!
A DV drop down list can hold up to 32,767 items. If you're not getting a "blank selection" at the end of the list you probably need to change the formula for the dynamic range. Maybe to this: =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J)+1) Biff "Graham Haughs" wrote in message ... I have a named dynamic range with the source =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J),1) Thia is set up as the data validation list in a cell with a drop down list It is set so that once the drop down is activated there is the option of selecting a blank from the bottom of the list so that if you activate the cell in error you don't have to delete any entry you are forced to make, you can select the blank and leave the cell blank. This works fine until the list gets quite long at which time there is no option of a blank.I don't know the number in the list where this starts but certainly a list over 100 has this problem. On shorter lists the blank is selectable. The selection is correct as when you check the named list it shows the correct range. I would welcome any views on solving this if it can be resolved. Kind regards Graham Haughs Turriff Scotland |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing blank in Dynamic range
I tried both the original formula and Biff's variation and the DV dropdown
stopped displaying the extra blank row if the list included more than 148 items. I don't know how this will work with the design of your spreadsheet, but I was able to have a blank row in the drop down and display all the data (I tested with 1000 rows) if I put the blank row at the TOP of the named range. This is more convenient for your users who accidentally click the dropdown and want to leave it blank. I used this formula to craete the named range: =OFFSET(Data!$J$1,0,0,COUNTA(Data!$J:$J)+1,1) Hope this helps, Hutch "Biff" wrote: Hi! A DV drop down list can hold up to 32,767 items. If you're not getting a "blank selection" at the end of the list you probably need to change the formula for the dynamic range. Maybe to this: =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J)+1) Biff "Graham Haughs" wrote in message ... I have a named dynamic range with the source =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J),1) Thia is set up as the data validation list in a cell with a drop down list It is set so that once the drop down is activated there is the option of selecting a blank from the bottom of the list so that if you activate the cell in error you don't have to delete any entry you are forced to make, you can select the blank and leave the cell blank. This works fine until the list gets quite long at which time there is no option of a blank.I don't know the number in the list where this starts but certainly a list over 100 has this problem. On shorter lists the blank is selectable. The selection is correct as when you check the named list it shows the correct range. I would welcome any views on solving this if it can be resolved. Kind regards Graham Haughs Turriff Scotland |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing blank in Dynamic range
Hi Biff, nice to hear from you again. I used your formula which gives
two blanks at end of list but they will still not appear in the drop down list. It will on smaller lists but not on the longer ones I am using. Graham Biff wrote: Hi! A DV drop down list can hold up to 32,767 items. If you're not getting a "blank selection" at the end of the list you probably need to change the formula for the dynamic range. Maybe to this: =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J)+1) Biff "Graham Haughs" wrote in message ... I have a named dynamic range with the source =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J),1) Thia is set up as the data validation list in a cell with a drop down list It is set so that once the drop down is activated there is the option of selecting a blank from the bottom of the list so that if you activate the cell in error you don't have to delete any entry you are forced to make, you can select the blank and leave the cell blank. This works fine until the list gets quite long at which time there is no option of a blank.I don't know the number in the list where this starts but certainly a list over 100 has this problem. On shorter lists the blank is selectable. The selection is correct as when you check the named list it shows the correct range. I would welcome any views on solving this if it can be resolved. Kind regards Graham Haughs Turriff Scotland |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing blank in Dynamic range
I'm having no trouble getting the blank selection no matter how many items
are in the list (up to the limit) but I like your idea about putting the empty cell at the top of the range. That way the user doesn't have to scroll to the bottom of the drop down to make that selection. Biff "Tom Hutchins" wrote in message ... I tried both the original formula and Biff's variation and the DV dropdown stopped displaying the extra blank row if the list included more than 148 items. I don't know how this will work with the design of your spreadsheet, but I was able to have a blank row in the drop down and display all the data (I tested with 1000 rows) if I put the blank row at the TOP of the named range. This is more convenient for your users who accidentally click the dropdown and want to leave it blank. I used this formula to craete the named range: =OFFSET(Data!$J$1,0,0,COUNTA(Data!$J:$J)+1,1) Hope this helps, Hutch "Biff" wrote: Hi! A DV drop down list can hold up to 32,767 items. If you're not getting a "blank selection" at the end of the list you probably need to change the formula for the dynamic range. Maybe to this: =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J)+1) Biff "Graham Haughs" wrote in message ... I have a named dynamic range with the source =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J),1) Thia is set up as the data validation list in a cell with a drop down list It is set so that once the drop down is activated there is the option of selecting a blank from the bottom of the list so that if you activate the cell in error you don't have to delete any entry you are forced to make, you can select the blank and leave the cell blank. This works fine until the list gets quite long at which time there is no option of a blank.I don't know the number in the list where this starts but certainly a list over 100 has this problem. On shorter lists the blank is selectable. The selection is correct as when you check the named list it shows the correct range. I would welcome any views on solving this if it can be resolved. Kind regards Graham Haughs Turriff Scotland |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing blank in Dynamic range
Nice one Hutch, it is useful to have the blank at the top of the list
and although I have headers on the source lists this can be modified to accomodate this. Glad you managed to replicate the problem, I was beginning to doubt my sanity. Thanks to all. Graham Tom Hutchins wrote: I tried both the original formula and Biff's variation and the DV dropdown stopped displaying the extra blank row if the list included more than 148 items. I don't know how this will work with the design of your spreadsheet, but I was able to have a blank row in the drop down and display all the data (I tested with 1000 rows) if I put the blank row at the TOP of the named range. This is more convenient for your users who accidentally click the dropdown and want to leave it blank. I used this formula to craete the named range: =OFFSET(Data!$J$1,0,0,COUNTA(Data!$J:$J)+1,1) Hope this helps, Hutch "Biff" wrote: Hi! A DV drop down list can hold up to 32,767 items. If you're not getting a "blank selection" at the end of the list you probably need to change the formula for the dynamic range. Maybe to this: =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J)+1) Biff "Graham Haughs" wrote in message ... I have a named dynamic range with the source =OFFSET(Data!$J$2,0,0,COUNTA(Data!$J:$J),1) Thia is set up as the data validation list in a cell with a drop down list It is set so that once the drop down is activated there is the option of selecting a blank from the bottom of the list so that if you activate the cell in error you don't have to delete any entry you are forced to make, you can select the blank and leave the cell blank. This works fine until the list gets quite long at which time there is no option of a blank.I don't know the number in the list where this starts but certainly a list over 100 has this problem. On shorter lists the blank is selectable. The selection is correct as when you check the named list it shows the correct range. I would welcome any views on solving this if it can be resolved. Kind regards Graham Haughs Turriff Scotland |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect and Dynamic Range | Excel Worksheet Functions | |||
Dynamic range name - odd results | Excel Worksheet Functions | |||
WEEKNUM to dynamic range name | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
repost: plz help- dynamic range with gaps? | Excel Discussion (Misc queries) |