Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to design data entry validation?
We enter daily expenses into a spreadsheet.
We have a list of categories in one section (range) of the spreadsheet. How can I ensure that the category in the expense log is one of the existing categories in the list? First, could we have a drop-down menu that is automagically updated if/ when we add categories to the list? If so, can someone outline the design? I have no idea. Alternatively, can we have a pop-up message that simply says the category is incorrect? Does that have to be a (event) macro? If so, can someone outline the design? I have never seen that kind of macro. How could the solution (macro or what-have-you) get updated automagically if/when we add categories to the list? I 'spose I could have a named range, which may or may not also need to be updated when we update the list. Perhaps a clever specification of the range and a discipline for updating the list (viz. inserting between blank rows) can avoid changing the named range definition. Of course, I know that could have a column that does a lookup and displays a non-blank symbol if the corresponding cell in the category column does not match. In fact, that is what we have now. But it is "not working" for us insofar as the user (not I) still fails to make the necessary correction (sigh). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to design data entry validation?
If you make the named range a dynamic range, then use of Data Validation
should meet your needs. See: http://www.contextures.com/xlNames01.html#Dynamic http://www.contextures.com/xlDataVal01.html HTH " wrote: We enter daily expenses into a spreadsheet. We have a list of categories in one section (range) of the spreadsheet. How can I ensure that the category in the expense log is one of the existing categories in the list? First, could we have a drop-down menu that is automagically updated if/ when we add categories to the list? If so, can someone outline the design? I have no idea. Alternatively, can we have a pop-up message that simply says the category is incorrect? Does that have to be a (event) macro? If so, can someone outline the design? I have never seen that kind of macro. How could the solution (macro or what-have-you) get updated automagically if/when we add categories to the list? I 'spose I could have a named range, which may or may not also need to be updated when we update the list. Perhaps a clever specification of the range and a discipline for updating the list (viz. inserting between blank rows) can avoid changing the named range definition. Of course, I know that could have a column that does a lookup and displays a non-blank symbol if the corresponding cell in the category column does not match. In fact, that is what we have now. But it is "not working" for us insofar as the user (not I) still fails to make the necessary correction (sigh). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to design data entry validation?
Another way would be to create a list, if your version is 2003, or a table if
it's 2007. 1) Enter all valid categories in that list or table 2) Define a name conterminous, or having the same limits, as the list or table 3) Data Validation Settings list =Name 4) Data Validation Error Alert Error message "Invalid Category" As the table expands or contracts, so does the defined name. "Debra Dalgleish" wrote: You can use dynamic ranges for the option lists, as described he http://www.contextures.com/xlNames01.html Then, refer to those names in the data validation dialog boxes: http://www.contextures.com/xlDataVal01.html Data validation can include input and error messages: http://www.contextures.com/xlDataVal04.html wrote: We enter daily expenses into a spreadsheet. We have a list of categories in one section (range) of the spreadsheet. How can I ensure that the category in the expense log is one of the existing categories in the list? First, could we have a drop-down menu that is automagically updated if/ when we add categories to the list? If so, can someone outline the design? I have no idea. Alternatively, can we have a pop-up message that simply says the category is incorrect? Does that have to be a (event) macro? If so, can someone outline the design? I have never seen that kind of macro. How could the solution (macro or what-have-you) get updated automagically if/when we add categories to the list? I 'spose I could have a named range, which may or may not also need to be updated when we update the list. Perhaps a clever specification of the range and a discipline for updating the list (viz. inserting between blank rows) can avoid changing the named range definition. Of course, I know that could have a column that does a lookup and displays a non-blank symbol if the corresponding cell in the category column does not match. In fact, that is what we have now. But it is "not working" for us insofar as the user (not I) still fails to make the necessary correction (sigh). -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Data Entry Validation | Excel Worksheet Functions | |||
Unique entry - Data Validation | Excel Discussion (Misc queries) | |||
Data Validation: stop blank entry | Excel Discussion (Misc queries) | |||
Data Validation / Cell Entry | Excel Discussion (Misc queries) | |||
Combining conditions for data entry validation | Excel Worksheet Functions |