How to design data entry validation?

[nomail1983]

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]

You can use dynamic ranges for the option lists, as described here:

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

 

[Guest]

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

 

[Guest]

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.