Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 39

Thread: Would someone please advise on a newbies code?

  1. #21
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by KristenB View Post
    Thank you, Bob! I have attached my updated file.
    Where Kristen, I don't see it.

    Quote Originally Posted by KristenB View Post
    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).

  2. #22
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Bob Phillips View Post
    Where Kristen, I don't see it.

    [LEFT][COLOR=#222222][FONT=Verdana]
    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!

  3. #23
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    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

  4. #24
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Bob Phillips View Post
    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!

  5. #25
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by KristenB View Post
    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

    =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.

    Quote Originally Posted by KristenB View Post
    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?

    Quote Originally Posted by KristenB View Post
    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.,

  6. #26
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Bob Phillips View Post
    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!

    Quote Originally Posted by Bob Phillips View Post
    [LEFT][COLOR=#222222][FONT=Verdana]
    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"


    Quote Originally Posted by Bob Phillips View Post
    [LEFT][COLOR=#222222][FONT=Verdana]
    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.

    Quote Originally Posted by Bob Phillips View Post
    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.
    Attached Files Attached Files

  7. #27
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    119
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Quote Originally Posted by Bob Phillips View Post

    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!

  8. #28
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Could I query the rule you mentioned for midday meals

    Quote Originally Posted by KristenB View Post

    - 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 by Bob Phillips; 2020-05-26 at 01:07 AM.

  9. #29
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Bob Phillips View Post
    Could I query the rule you mentioned for midday meals
    I'm not sure what you mean?

    Quote Originally Posted by Bob Phillips View Post
    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.

    Quote Originally Posted by Bob Phillips View Post
    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.

    Quote Originally Posted by Bob Phillips View Post
    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.

  10. #30
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Another day, another session on this bl##dy expenses form .

    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.


Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •