Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation: using Custom, NOT List, to get non-blank cells
Hi all,
This is a bit tricky question. We all know that cell with Data Validation's List includes items contained in a specified named range. Fine. I, instead, want to do it with Custom using some kinf of formula, so that users do NOT need to change the named range when a new item was added. Is this possible? I have some items in one column and thinking of going from top, say, A1 until the last non-blank cell downward, say, A10. How can I specify this in Data Validation's custom option? Thanks for your time. --- Tetsuya Oguma |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation: using Custom, NOT List, to get non-blank cells
Hi Tetsuya
Here's one way: Choose "List" and enter this formula: =OFFSET(A1,,,COUNTA($A:$A)) Only use column A for elements of the list. -- Best Regards Leo Heuser Followup to newsgroup only please. "Tetsuya Oguma" <Tetsuya skrev i en meddelelse ... Hi all, This is a bit tricky question. We all know that cell with Data Validation's List includes items contained in a specified named range. Fine. I, instead, want to do it with Custom using some kinf of formula, so that users do NOT need to change the named range when a new item was added. Is this possible? I have some items in one column and thinking of going from top, say, A1 until the last non-blank cell downward, say, A10. How can I specify this in Data Validation's custom option? Thanks for your time. --- Tetsuya Oguma |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data validation: using Custom, NOT List, to get non-blank cells
Hi Tetsuya,
This is a bit tricky question. We all know that cell with Data Validation's List includes items contained in a specified named range. Fine. I, instead, want to do it with Custom using some kinf of formula, so that users do NOT need to change the named range when a new item was added. Is this possible? This can be achieved by using a dynamic name to refer to the list. So instead of the name being, say, =$A$1:$A$10, you define it as =OFFSET($A$1,0,0,COUNTA($A:$A),1) (assuming there's nothing else in column A). That name will then automatically refer to however many items are in the list that starts at A1, and the name can be used to drive DV's list. Regards Stephen Bullen Microsoft MVP - Excel www.oaltd.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Data Validation Blank Cells | Excel Worksheet Functions | |||
Data Validation on blank cells | Excel Worksheet Functions | |||
blank entries in data validation list | Excel Worksheet Functions | |||
Adding a blank in Data Validation List? | Excel Discussion (Misc queries) |