Limit data entry with Data valitadtion based on date

Dilipandey:

There is data in the form already, but you may need to scroll up to the top of the sheet (there are over 150 lines in the form) to find the data that I included. The work flow is supposed to be that a driver cannot record "field" miles on the same day as the driver has "Home" miles on the form, but any day that they do record "Field" miles, if they make more than one trip, they are required to record them seperately.

i.e. On Tuesday, the driver only reports to the home office and goes nowhere else, thus s/he records Tuesdays as an "H". On Wednesday, the driver takes five seperate trips into the field, thus recording five seperate "F" trips for the same day.
The form needs to take a look at the travel status field, and then count the days that were marked "F" not just the occurances of "F" in that collum.

Also, the datavalidation in collumn J needs to prevent a user from entering in more than one "H" for a single day, and also prevent the user from entering in both an "F" and an "H" for a single day, while ALLOWING the user to enter MULTIPLE "F" entries if there is no "H" for that same day.

Thanks for looking at this!

Originally Posted by dilipandey

Hi Phraedrique,

You have asked multiple things in one post. Below are my remarks on each of them, separated below:-

1) A driver cannot record "field" miles if he had entered "H" under column "TRAVEL STATUS".. right?
2) Driver can enter "F" any day (or number of days) but then they must not enter any of these days with "H"under column "TRAVEL STATUS".. right?
3) So a driver can enter either Fs for more more rows... but Hs can be only one row .. keeping above scenario in mid
4) Also I need to know which column is meant for recording "field" miles?
5) I do not see five separate Fs trip for the same day (i.e, Tuesday 1-18-2012) as I see only two rows related to this which are row 18 and 19.
6) your last para for validation is crystal clear.. thx for that.

Please provide your remarks to my above points. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

1) correct, no field miles FOR THAT DAY if the driver records "Home" miles FOR THAT DAY.
2) correct
3) Correct, multile entries of "F" for a single date, or a single entry of H for a single date
4) All miles are recorded in the same collumn and a calculation is done based on the "H" or "F" status in the row in a seperate cell. this is done by subtracting the total home miles (currently incorrectly calculated by the original author of the workbook by multiplyint the Round Trip Milage in U8 by the number of days of "Home travel") from the total miles traveled (=sum(h11:h139)
5) sorry about that... I was suggesting a POSSIBLE instance, not actual sample data in the example worksheet. i.e. a person COULD have 5 or more "F" trips on a single day and they all need to count as a single instance in the "Total Field Days Used" value (cell n142)
6) well a stopped (analog) clock is correct alt least twice per day... (thanks...)

Hummm... thought that this was working, but for some reason, while I am able to enter as many "F" values in the travel Status column, I am not able to enter any "H" values if there are any date entries in colum B at all.

errr. perhaps we are not on the same page here for point #2. The user needs to be able to enter an "H" on any given day as long as there is no other entry for the same day (neither "H" nor "F"), AND must be allowed to enter multiple "F" entries in that same "Travel Status" column.
i.e.:

Column B	Column J
1-1-12	H
1-2-12	F
1-2-12	F
1-2-12	F
1-2-12	F
1-3-12	F
1-4-12	H
1-5-12	H
1-6-12	F
1-6-12	F

Would be a valid set of data in the worksheet. So what I need to be able to do is limit data entry so that each "H" value has a unique date with no other values allowed for that date, and that multiple rows of "F" values are allowed on single dates, but no "H" is allowed to be on the same date as an "F" value.

Unfortunately, while the "H" value can be entered for disticnt dates, the formula is now allowing "F" value to be entered into the field on the same date as an "H" value.
Is this somethignthat Excel just cannot do?

errr... no, dates CANNOT have BOTH an H and an F in them. Dates can only have a SINGLE "H", OR multiple "F"'s.

Originally Posted by dilipandey

Dear Phraedrique..

You are contradicting your statement.. you said "dates CANNOT have BOTH an H and an F in them" and just after it you are saying "Dates can only have a SINGLE "H", OR multiple "F"'s...

I understand that a date can have only one H and multiple Fs.. and this is what I am able to enter into the spreadsheet.

Would suggest you to test the spreadsheet basis above logic and highlight the cells with comments (right click - insert comment). Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

First, I want to thank you for your efforts here... I am realy trying NOT to complicate things, but you seem to have misunderstood a basic need here. The underlined and highlighted statement above is false.

If you look at the table in post #9 you will see that none of the dates contain BOTH an H AND an F.

Additionally, While it is possible for a single date to contain multiple F's, no date can contain more than one "H".
2-Jan h Unique date, unique H
3-Jan f Unique date, unique F
4-Jan h Unique date, unique H
5-Jan f unique date, multiple f
5-Jan f unique date, multiple f
5-Jan f unique date, multiple f
8-Jan h Unique date, unique H
9-Jan h Unique date, unique H
10-Jan f unique date, multiple f
10-Jan f unique date, multiple f
10-Jan f unique date, multiple f
11-Jan h Unique date, unique H

M805-sandbox (2).xlsmYes, I just tried the one from post #12 again, and I am still able to enter H values in dates that already have F values in them. I have made some comments to the file and will attach it below.

PL see attached file.I have added code for sheet change for M805 (3).Used AC1:AC3 for validation.
clarifications welcome.

Originally Posted by kvsrinivasamurthy

PL see attached file.I have added code for sheet change for M805 (3).Used AC1:AC3 for validation.
clarifications welcome.

Interesting attempt, I liked the formulas in AC1:AC3 but unfortunately I am still able to enter H and F values on the same date.
So, again, I need to be able to limit all dates to a single value (i.e. all entries for 1/12 can be EITHER an "H" or an "F") and for any date that has an "H" that then prevents any further entries of any kind for that date, while allowing any date that has an "F" value in it to have multiple "F" entries.

Pl see the attached file.