guided data input

Hi,

Hope that somebody is able to help me to ensure that people complete a detailed spread sheet with ALL data, as this is a compliance requirement. 

Trying to keep it simple, we have a screen with Name, Position, Date, Pest (we are a pest control company), question and comment text, which we will number 1 - 6 respectively.  They all need to be completed in order to meet ISO compliance. It is more complex than this, but it gives you an idea of what we are trying to achieve.

We are now re-writing the sheet to point out that the sheet is 'in error' when only part of the data has been entered.  We highlight the uncompleted boxes, using conditional formatting, in effect saying if(4=blank,highlight in orange).  As the person completes all of the sections the orange gradually disappears until all 6 items have received data.  Is there a way of doing the 'orange bit' a little more gradually, otherwise when they first start by entering their name sections 2-5 all highlight in orange, making them a little nervous.

Working through the logic, to bring highlighting missing data in an order, this gives me the following logic:

When they complete the name we want position (2) to highlight.  If they complete position first we want name (1) to highlight.  As they enter more data it continues to highlight the section called 'HIGH', until at 21 all items have been entered, or at 22 no items have been entered and nothing is highlighted.

This probably needs to use an array, but this is outside of my skills.  We need it to return the HIGH number so that the conditional formatting can act accordingly.

Is there anybody that can help with this query?

Thanks in anticipation.

Graham - Nutrastat

 Graham,

I'm not going to answer your question directly - one, I think you are making the logic pretty complicated to build (and maintain), UNLESS you need to step the user from cell to cell in a specific sequence. Two, I just did a utility with similar requirements - here is my approach.

For every cell that requires user input, I put an indicator in the adjascent the cell. (I like to use Marlett font, Bold, size 8, colour orange: Sorry - I haven't been able to paste the image - maybe Nick can help.) Then the conditional format is on the indicator cell, but the formula is constructed to refer to the data entry cell.

The usefulness of this approach - its simple, and a large number of indicators doesn't intimidate the user. Also, they disappear as the form is filled in.

The last thing I do is put a similar indicator beside the SAVE button, and disable the SAVE button until all the conditions are met.

Hope this alternate approach helps.

 (Check out the linked file in my response - it worked after all)

 Hi,

Having done this sort of thing before, I used a UDF.

I found that it to be excellent (once I had been through the pain of working out how to write it and get it to be 100% accurate).

If your input was in columns B to G, then I put a "message" cell in column H explaining what was missing.

I didn't worry about the order, just did it sequentially - for example

Default message woudl be "Enter name"

Once that was complete it would say that the next column needed to be completed and so on until everything was complete and it said OK.

It might not work for you, but we used the same thing for 5 years and found it very useful (we extended it to look at the quality of input as well).

1 tip I learned was to write each check as an IF function in the adjacent cells, and then link them all together.

IF you don't want to do a UDF, then use the independent IF statements, and check that you get 6 OK's before switching off any Conditional Formatting.

HTH

Hi Alex,

Many thanks for your prompt reply and the idea contained therein.  Very useful, and I will use your idea coupled with the other one received, as they both work for me.

Thanks for your time in this matter.

Best regards,

Graham

Hi Alan,

Many thanks for your prompt reply and the idea contained therein.  Very useful, and I will use your idea coupled with the other one received, as they both work for me.

Thanks for your time in this matter.

Best regards,

Graham