Ensure data entered where required - how, please?

[Edward]

I'm not an Excel expert so please be gentle with me.

I've been asked to design a form to enter data that will later be used
in a SQL Server DTS package to populate a database.

I've restricted the worksheet to only certain cells, and used drop-
down lists where possible. What I really want to do is to try to
ensure that users enter ALL required data - i.e. if they enter data on
a row they put something in all the required columns.

I could use a button and VBA to achieve this, but I think the client
would rather I didn't (for security reasons: the worksheet will be
circulated to many hundreds of employees). Can anyone think of any
nifty tricks to ensure data is entered where required?

Many thanks

Edward

 

[Dave Peterson]

You could use a column that indicates an error--maybe a new column A.

=if(counta(b2:G2)=6,"","Please fill in all the cells!")
(and give it a nice big, bold red format)

Apply data|filter|autofilter to that column and the user could even filter to
show non-blanks to see the problem rows.

 

[Luke M]

Well, it depends on what the final action is. Does the user click a button to
save the form somewhere, or do something? To the point, how will XL know the
difference between someone who is still working on the form, and someone who
is done?

A basic idea would be to use a COUNTA formula that checks all your required
cells. So, for one row
=IF(COUNTA(A2,B2,D2)=3,"Complete","Incomplete")
would be a type of basic check.

You could also include a similar formula in Data - Validation. For instance,
let's say you have a signature cell or something. You could make the
validation on that cell
=COUNTA(A2,B2,D2)=3

Now, the user will only be able to "sign" the cell once A2, B2, and D2 have
been filled in with something.

But again, to restress beginning point, the main thought to think on is how
is XL to know that someone is "done" and the data should be verified?

 

[Edward]

Well, it depends on what the final action is. Does the user click a button to
save the form somewhere, or do something? To the point, how will XL know the
difference between someone who is still working on the form, and someone who
is done?

A basic idea would be to use a COUNTA formula that checks all your required
cells. So, for one row
=IF(COUNTA(A2,B2,D2)=3,"Complete","Incomplete")
would be a type of basic check.

You could also include a similar formula in Data - Validation. For instance,
let's say you have a signature cell or something. You could make the
validation on that cell
=COUNTA(A2,B2,D2)=3

Now, the user will only be able to "sign" the cell once A2, B2, and D2 have
been filled in with something.

But again, to restress beginning point, the main thought to think on is how
is XL to know that someone is "done" and the data should be verified?

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Thanks Luke and Dave. I think I see a way around this - get the user
to check a check box when they think they're complete. At that stage
validate the form, and highlight non-conforming cells. Or something.

Edward