| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation list with blanks at the bottom
I have a list of infomation from cells A2:A107 which is used as a reference
for a validation list. I have used the this code: =INDIRECT("sheet2!A2:A200") I have used this code because the list is constantly updated and this will mean if I enter a new value at A108 I wont have to update the validation list code. However my problem is on the list when its dropped down it does not ignore the blanks. It shows 93 blank spaces after the values. I have tried clicking the Ignore-Blanks tick box yet it does not work. any ideas? P.S am usuing Excel 200 Andrew |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with blanks at the bottom
I think you'll be better off using a dynamic range name.
Check how Debra Dalgleish does it: http://contextures.com/xlNames01.html#Dynamic Andrew wrote: I have a list of infomation from cells A2:A107 which is used as a reference for a validation list. I have used the this code: =INDIRECT("sheet2!A2:A200") I have used this code because the list is constantly updated and this will mean if I enter a new value at A108 I wont have to update the validation list code. However my problem is on the list when its dropped down it does not ignore the blanks. It shows 93 blank spaces after the values. I have tried clicking the Ignore-Blanks tick box yet it does not work. any ideas? P.S am usuing Excel 200 Andrew -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with blanks at the bottom
fantastic il try it now
"Dave Peterson" wrote: I think you'll be better off using a dynamic range name. Check how Debra Dalgleish does it: http://contextures.com/xlNames01.html#Dynamic Andrew wrote: I have a list of infomation from cells A2:A107 which is used as a reference for a validation list. I have used the this code: =INDIRECT("sheet2!A2:A200") I have used this code because the list is constantly updated and this will mean if I enter a new value at A108 I wont have to update the validation list code. However my problem is on the list when its dropped down it does not ignore the blanks. It shows 93 blank spaces after the values. I have tried clicking the Ignore-Blanks tick box yet it does not work. any ideas? P.S am usuing Excel 200 Andrew -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with blanks at the bottom
It worked your a star
"Dave Peterson" wrote: I think you'll be better off using a dynamic range name. Check how Debra Dalgleish does it: http://contextures.com/xlNames01.html#Dynamic Andrew wrote: I have a list of infomation from cells A2:A107 which is used as a reference for a validation list. I have used the this code: =INDIRECT("sheet2!A2:A200") I have used this code because the list is constantly updated and this will mean if I enter a new value at A108 I wont have to update the validation list code. However my problem is on the list when its dropped down it does not ignore the blanks. It shows 93 blank spaces after the values. I have tried clicking the Ignore-Blanks tick box yet it does not work. any ideas? P.S am usuing Excel 200 Andrew -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with blanks at the bottom
I'm sure Debra appreciates that her site has helped another user.
Andrew wrote: It worked your a star "Dave Peterson" wrote: I think you'll be better off using a dynamic range name. Check how Debra Dalgleish does it: http://contextures.com/xlNames01.html#Dynamic Andrew wrote: I have a list of infomation from cells A2:A107 which is used as a reference for a validation list. I have used the this code: =INDIRECT("sheet2!A2:A200") I have used this code because the list is constantly updated and this will mean if I enter a new value at A108 I wont have to update the validation list code. However my problem is on the list when its dropped down it does not ignore the blanks. It shows 93 blank spaces after the values. I have tried clicking the Ignore-Blanks tick box yet it does not work. any ideas? P.S am usuing Excel 200 Andrew -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to password protect a validation list? | Excel Discussion (Misc queries) | |||
| auto updating list | Excel Worksheet Functions | |||
| Changing Where Data Validation List Boxes Open in Mac Excel 2004 | Excel Discussion (Misc queries) | |||
| Data Validation lists - entering value not in list | Excel Discussion (Misc queries) | |||
| list validation using list validation... | Excel Worksheet Functions | |||
Linear Mode
