Excel dropdowns w/o blanks

[[email protected]]

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!!!

[Earl Kiosterud]

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 Kiosterud
www.smokeylake.com

<[email protected]> wrote in message
news:[email protected]...
> 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!!!
>

[[email protected]]

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

[Earl Kiosterud]

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.
--
Earl Kiosterud
www.smokeylake.com

<[email protected]> wrote in message
news:[email protected]...
> 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
>