Excel dropdowns w/o blanks

  • Thread starter Thread starter darrellburress
  • Start date Start date
D

darrellburress

Hello,

I have built a spreadsheet which requires data to be entered in order
for the sheet to work properly. My problem is even though there IS NOT
a blank option in the drop down, you can select the cell and hit
<delete>. This effectively enters a "blank" entry and renders the
outgoing data worthless....

How can I keep users from doing this?

Thank you for your help!!!
 
Darrell,

I think the dropdown you're referring to is Data Validation. It doesn't get
involved when a cell is cleared (delete key), pasted into, another cell
moved there, and such things. It's strictly for keying stuff into the
cell -- it doesn't monitor anything else. There are ways with event macros
that can trap such conditions, or simply a formula that monitors a column
(or row or single cell) that can light up in bold red when such conditions
exist. But you have to say more about the sheet. For example, is it a
column? Can the cell be empty except when there are entries in other cells
of the row? Stuff like that.
 
Earl,

You are right... I did use data/valadation.... is this not the right
way?

i am basically building a sheet to determine how much tax to withold (
I know this has been done before, but I want to do one myself...) from
a paycheck. The two entries using dropdowns are for marital status
(single, married), and pay cycle (weekly, bi-weekly, Semi-monthly,
monthly). I need these two components to be valid in order to get the
desired answer.

hmmm.... I can put a line in my solution window checking if those are
blank but the answer im really looking for is to force an input and not
allow blanks....

thanks again!!!!!

DarrellB
 
Darrell,

Data Validation is fine, but won't care if an entry is never made, or
cleared (Delete key) later.

Presumably, there are several rows, one for each paycheck. Right? A
formula could monitor the Marital and Pay Cycle cells, but would need to
examine other cells in the row to determine if the row has been used yet.
Here's a possible solution. This will be a mess if you aren't using a
non-proportional font like Courier.

A B C
1 Date Marital Pay Cycle
2 1/1/05 M 1
3 1/31/05 M 2

In D4, copied down, you could have:
=IF(AND(A2<>"",OR(B2="",C2="")),"*","")

This cell could be formatted red, bold. Change the asterisk to whatever you
want.
 
Back
Top