Would someone please advise on a newbies code?

Thank you, Bob! I have attached my updated file.
Where Kristen, I don't see it.

I was working on trying to figure out how to lock down specific fields with formulas when you responded to me. I have figured out how to protect the sheets okay but not the cells that I need to lock on the Travel Expense Form worksheet. I am still working on that.
You need to select all of the cells on the sheet and unlock them, Home>Format>Protection>Lock Cell (it will be shaded as they are locked), then select the cells to lock and repeat the same steps (it won't be shaded this time).
 
Where Kristen, I don't see it.


You need to select all of the cells on the sheet and unlock them, Home>Format>Protection>Lock Cell (it will be shaded as they are locked), then select the cells to lock and repeat the same steps (it won't be shaded this time).


I will work on locking fields while you look at my form. It is attached now... I'm sorry about that!
 

Attachments

  • Final TravelForm 04272020 Continuation for Forum (1).xlsm
    128.4 KB · Views: 10
The meal rules seem very simple to me. I am wondering why you were steered down the path you were, so can I check I have the rules correct:

- Morning meal
- non-employee always
- employee has to depart more than 1 hour before their normal start time, and has to be out for more than 3 hours between 10:00-15:00

- Midday meal
- non-employee always
- employee has to be out for more than 3 hours between 10:00-15:00

- Evening meal
- all employees, return more than 1 hour after their normal finish time, and have
to be out for more than 3 hours between 10:00-15:00
 
The meal rules seem very simple to me. I am wondering why you were steered down the path you were, so can I check I have the rules correct:

- Morning meal
- non-employee always
- employee has to depart more than 1 hour before their normal start time, and has to be out for more than 3 hours between 10:00-15:00

- Midday meal
- non-employee always
- employee has to be out for more than 3 hours between 10:00-15:00

- Evening meal
- all employees, return more than 1 hour after their normal finish time, and have
to be out for more than 3 hours between 10:00-15:00


Actually, they are as follows:

- Morning meal
- non-employee has to be out for more than 3 hours between 12:01-10:00
- employee has to depart more than 1 hour before their normal start time, and has to be out for more than 3 hours between 12:01-10:00

- Midday meal
- non-employee has to be out for more than 3 hours between 10:01-15:00
- employee has to depart more than 1 hour before normal start time, and has to be out for more than 3 hours between 10:01-15:00

- Evening meal
- non-employee has to be out for more than 3 hours between 15:01-24:00
- employee has to be out more than 1 hour past normal finish time, and have to be out for more than 3 hours between 15:01-24:00

Then, the expense codes also come into play depending on if they are an employee or not and if they are in-state or out-of-state and whether it is an overnight trip or return trip.

Can you tell I am sitting at my computer working on the document also? :) I have the cells locked down and am now trying to also do coding to remove 'added' rows when the form is cleared. I'll figure it out... just takes a little time to research everything. :) Thank you so much for helping me figure this out! The meals piece was painful for me and even though I reached out for help, all I got was what I have incorporated. If you can figure this out, I will certainly call you my hero! :)
 
Actually, they are as follows:
Thanks for that, they make more sense than what I had. But I don't think that is what you formulae do. For instance, the eligible morning meal formula, AD14 says

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF((V14="N"),"T", IF(AND(V14="Y",W14="Y",X14="Y"),"T","F"))

V14 says Y or N for employee, that suggests to me a non-employee always gets a morning meal (doesn't sound appropriate, but the formula seems to give it).[/FONT]
I have just tried to add a row for a non-employee, leaving at 10:00 am and returning at 11:00 am and the form shows morning and midday meals checked.

Then, the expense codes also come into play depending on if they are an employee or not and if they are in-state or out-of-state and whether it is an overnight trip or return trip.
I can see the in-state/out-of-state in columns Q:S, but where is the expense code checked?

I have the cells locked down and am now trying to also do coding to remove 'added' rows when the form is cleared. I'll figure it out... just takes a little time to research everything.
Why don't you let me to that bit, I am changing so much code (trying to make it easier to read and more easily maintained) that I think we make be changing the same code, and therefor might lose it.

BTW, can you also tell me why that you have formulas in B11 and D11 to check if non-employee and suppress the input, seems pointless as you don't suppress E11, F11, and H11, and you hide the row anyway.,
 
Thanks for that, they make more sense than what I had. But I don't think that is what you formulae do. For instance, the eligible morning meal formula, AD14 says

=IF((V14="N"),"T", IF(AND(V14="Y",W14="Y",X14="Y"),"T","F"))

V14 says Y or N for employee, that suggests to me a non-employee always gets a morning meal (doesn't sound appropriate, but the formula seems to give it).

I have just tried to add a row for a non-employee, leaving at 10:00 am and returning at 11:00 am and the form shows morning and midday meals checked.

Oh no! Maybe I missed a piece in that? UGH... this is so much for a newby to take on. I have been testing as I go but I must have missed that piece. I'm glad you caught that! Thank you!


I can see the in-state/out-of-state in columns Q:S, but where is the expense code checked?


YIKES! I had a different sheet that I had used to calculate those and I deleted it, not thinking about those. The coding spreadsheet is in the attached workbook - tab "Travel Expense Codes"



Why don't you let me to that bit, I am changing so much code (trying to make it easier to read and more easily maintained) that I think we make be changing the same code, and therefor might lose it.


Okay, I think I have it but I will leave it alone until I hear back from you. :)

BTW, can you also tell me why that you have formulas in B11 and D11 to check if non-employee and suppress the input, seems pointless as you don't suppress E11, F11, and H11, and you hide the row anyway.,

Yes, if this is a non-employee, I have it hiding the 'Normal Work Hours' row since it wouldn't qualify. 'Normal Work Hours' only apply's for staff. Those are just formulas that I had as I was trying something and I didn't remove them when I decided to hide the row. Those are garbage.
 

Attachments

  • Final TravelForm 04272020 for forum.xlsm
    97.5 KB · Views: 3

I cannot believe someone wouldn't have jumped in to help, often we are tripping over each other. Most time an OP doesn't get a response is because the problem is horrendously complex (usually user-made complexity), or it has been so badly explained (or not explained) that no-one understands it

I spent a good half hour looking at the code when it was first posted as "Need help with a complex document?", a few weeks ago, but before I could respond the OP messaged that she had figured it out. Now that you've picked up the gauntlet, my feeling (and perhaps that of others here) is that the OP couldn't be in better hands!
 
Could I query the rule you mentioned for midday meals

- Midday meal
- non-employee has to be out for more than 3 hours between 10:01-15:00
- employee has to depart more than 1 hour before normal start time, and has to be out for more than 3 hours between 10:01-15:00

Why does an employee have to start more than 1 hour before normal start time to qualify for a midday meal allowance. Morning I understand than condition, but for a midday meal isn't 3 hours out between 10:01-15:00 sufficient (as for non-employees)?

Also, should the checks be for > 1 hour, 3 hours, or should they be for >= 1 hour, 3 hours. Seems harsh not to allow someone who starts at 6:00am to have breakfast!

BTW, your formulae only check for 3 hours out between 10:00 and 15:00 in all cases, not for the times you mentioned in your rule summary.
 
Last edited:
Could I query the rule you mentioned for midday meals

I'm not sure what you mean?

Why does an employee have to start more than 1 hour before normal start time to qualify for a midday meal allowance. Morning I understand than condition, but for a midday meal isn't 3 hours out between 10:01-15:00 sufficient (as for non-employees)?

Yes, I guess that would be correct. We wouldn't have anyone that starts their shift at 12:00 or anything I'm sure, so I think that would be fine. :)

Also, should the checks be for > 1 hour, 3 hours, or should they be for >= 1 hour, 3 hours. Seems harsh not to allow someone who starts at 6:00am to have breakfast!

They have to be greater than. :( I agree, seems harsh but staff has just learned to put down that they left at 5:59 instead of 6:00. LOL But the formulas but be greater than.

BTW, your formulae only check for 3 hours out between 10:00 and 15:00 in all cases, not for the times you mentioned in your rule summary.

Really? I thought it was working right. :( UGH... I must have missed testing those situations.
 
Another day, another session on this bl##dy expenses form :wink:.

I have got the form writing the meal allowance values, and the Go button code writes to ExpenseCodeSummary now. I want to test a bit more after I have ejoyed some of this lovely late spring weather over here.

I have a couple of questions for you:

- I have assumed that mileage, meals, lodging, and other expenses all write separate rows to
ExpenseCodeSummary, your code overwrote each in turn. That seems obvious to me, but one thing is not so obvious. I have a row that has no lodging amount as it was a return journey, and I am writing a row to
ExpenseCodeSummary with a zero amount. I can see logic in writing all expense codes even if zero, but I can also see that maybe they should not be written. What is your policy?

- you test the lodging value for 12. What is the significance of that?

- how to you record overnight stays? If they depart at say 10:00 am and arrive back at 8:00 am the next day, the time checks will be all to pot.

 
Another day, another session on this bl##dy expenses form :wink:.

I'm so sorry! I didn't mean to put it all on you. :( I was trying but am just too new, I guess. Too many things I don't understand and things that I wasn't getting help on no matter how much research I performed. I'm not sure if you noticed the date on the last file that I sent you with the coding formulas, but I have been working on this for a year now.

I have got the form writing the meal allowance values, and the Go button code writes to ExpenseCodeSummary now. I want to test a bit more after I have ejoyed some of this lovely late spring weather over here.

Of course!! Please don't let this form keep you from your 'off time' fun! I wouldn't want that!


I have a couple of questions for you:

- I have assumed that mileage, meals, lodging, and other expenses all write separate rows to ExpenseCodeSummary, your code overwrote each in turn. That seems obvious to me, but one thing is not so obvious. I have a row that has no lodging amount as it was a return journey, and I am writing a row to ExpenseCodeSummary with a zero amount. I can see logic in writing all expense codes even if zero, but I can also see that maybe they should not be written. What is your policy?

I thought the 'offset' was moving it down for each? :( Maybe that is the piece that I was missing. On the 'codes' spreadsheet there is a value that it should be looking at to know where to place the coding. No, if there is a zero value in one of the amount ranges, the value should not move to the Expense Code Processing spreadsheet. Only positive values and their project code and expense code should move to that sheet.



- you test the lodging value for 12. What is the significance of that?


There is a different expense code for lodging values of 12. If the traveler does not get a receipt, or if they stay at a family members house, which occasionally happens, then they can claim $12 for that night. When $12 is entered, that is what should determine the expense code for the reimbursement.


- how to you record overnight stays? If they depart at say 10:00 am and arrive back at 8:00 am the next day, the time checks will be all to pot.


When someone travels overnight, they are to report only until 11:59 p.m. that night. The next day begins a new day of travel and that would then start at 12:00 a.m. until they arrive back at their work location or home. They should never report two days travel in one.

I hope this helps. I can't wait to see what you do so I can learn this. I really do love to learn and am wanting to learn how to do other forms that we use. However, I can guarantee that they won't be nearly as difficult as this one!​
 
Last edited by a moderator:
Here is what I have got so far.

It is quite a bit different from yours, so give it a good airing. Check the meal eligibility works correctly, check it writes the correct meal amount, check it writes the corrrect expense codes.

I have locked the whole claim sheet apart from the user details, they cannot change any apart from that. I have removed all of the helper calculations in V:AM, all done by code now.

Enjoy, let me know how it goes.
 

Attachments

  • XLGuru 106462 - MTEG Travel Expenses.xlsm
    155.6 KB · Views: 24
Last edited:
BTW, didI read that right, you have a company aircraft?
 
BTW, didI read that right, you have a company aircraft?


It's actually state, but I removed all references to state because of privacy. LOL But yes, we do have a couple of helicopters, and I believe one other aircraft, but I am not sure what kind. :)
 
It's actually state, but I removed all references to state because of privacy. LOL But yes, we do have a couple of helicopters, and I believe one other aircraft, but I am not sure what kind. :)

Did I upset you? Please know that while we have a couple of aircraft, they are required for the business that we do. We don't have like any commercial jets or anything that we fly around in. :(
 
No! Whatever makes you think that?
 
I just hadn't heard anything and was afraid that you were mad at me and weren't going to help me anymore. LOL Consider me a little paranoid?
 
I posted my workbook in post #32, the one before I asked about the aircraft. Check it out.

I mentioned the aircraft because of somethging you said earlier, thinking that you worked for a company that wouldn't provide budget for some work to be done, but pays to run aircaraft (presumably for the top execs)!
 
OMG! I totally missed where you posted it! I'm so sorry!

I hear you.. that would upset me too. :) But we do not have aircraft for jetting around in, if we did, I would totally tell them to make the best ever and pay for it. However, to be honest, I enjoy learning new things and this was something that I really felt I would be able to do that would help staff on their travel documents. I can't wait to look it over and try it tonight. :) I have a little more work to do before I can but I plan on playing for a bit with it! Thank you so much. I will post back tomorrow and let you know how it works. :)
 
Back
Top