Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ensure data entered where required - how, please?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ensure data entered where required - how, please?
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. Edward wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ensure data entered where required - how, please?
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!* "Edward" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ensure data entered where required - how, please?
On Sep 28, 1:34*pm, Luke M wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ensure that required cells have beeb updated on saving the workboo | Excel Discussion (Misc queries) | |||
Required data in one sheet | Excel Worksheet Functions | |||
how to ensure that the chart will be deleted after i delete data | Charts and Charting in Excel | |||
Help required with Consolidating Data | Excel Worksheet Functions | |||
Data manipulation help required ! | Excel Worksheet Functions |