Posts by Excel Noob

    Hi norie,


    Thank you. I too have narrowed it down to the date format. The issue I've seen with column F is that neither I or my colleague are able to change the format of the column by right clicking. No matter what format I chose it doesn't change the data in column F. I have to double click on a cell before I can change the content of that cell. I thought if you set the date so it has the * in front of it the date should automatically be changed in each region?


    This is meant to be a workbook where you can run reports from sources such as LinkedIn and paste them into the data sheets so the Summary page can gather the required data. I need to be able to make this as easy to use as possible so people in the UK and EU won't have to worry about formatting.

    Hi,


    I'm looking for some help on, what to me is a strange error. I have created this workbook utilizing a sumproduct array on the Summary page. The formula works perfectly on my workbook but if I send it to a coworker all of the formulas on the Summary page come back with a value error. I have created the workbook in 2013 version and my coworker is opening it in 2016. There are no external data references, all of the data is contained within this workbook on different tabs. I'm in North America and my coworker is in Europe, I doubt this makes any difference though.


    Here's where is gets weird. If my coworker sends me back the copy that they saved, the error is present. If I double click on any of the errors on the Summary page and then simply press enter the error goes away. If I go to column F on the Update Engagement tab and either double click on any date, or just click on the filter for column F and then Okay, the error goes away. One thing I do notice is that if I double click on the date in column F it eliminates the leading zero. If my coworker tries any of these steps the error does not resolve.


    I'm sure it's probably something simple but it has me stumped. Thank you in advance for taking the time to look at this.

    Hi Roy,


    Thanks I posted that before I saw your code. I tried it and what it seems to be doing is Auto Filtering the Prospects Sheet, hiding anything that isn't closed. If I have to use Filtering then as you said there's no need for a second sheet or VBA.

    There's a couple of things I want to clarify.

    1. Rows 1 through 9 are hidden. They contain Data Validation lists.

    2. Header values are in A10 and across. All data that needs to be assessed reside in rows 11+ starting in column A.

    3. It is a basic Excel sheet, not a table.

    Hi,


    I have a working code (for the most part).


    I have 2 issues with this code which I can't figure out how to get past.


    Purpose of VBA


    Workbook contains 2 sheets, the first is named Prospects the second is named Closed

    When the command button is clicked, the entire Prospects Sheet should be scanned for the value of "Closed" in column P.

    Any row that has "Closed" in column P should be moved into the first available empty row(s) in the Closed Sheet

    Any row that is removed from the Prospect Sheet needs to be deleted and shifted up so that there are no empty rows between data and all formatting remains


    Issue 1


    When the "Closed" row is moved to the Closed Sheet I lose the formatting in that row on the Prospects Sheet. I have Data Validation set in certain columns on the Prospects Sheet and that's gone once the VBA runs.


    Issue 2


    The code seems to only work for one row at a time. I'd like the code to grab all of the rows that are marked as "Closed" in column P on the Prospects Sheet, following the same criteria as explained above.


    Thank you in advance,


    Robert


    Re: Calculate overtime based on 8 hour day / 44 hour week


    Hi Infomage,


    That works perfectly. It's actually really close to an iteration of the formula that I attempted as well. Thank you so much for your efforts and patience with this. I know I kind of led us down the wrong path for a bit. Once again my apologies for that. Members like you are what makes this site so valuable. Thank you again and I hope that you have a great holiday season!!

    Re: Calculate overtime based on 8 hour day / 44 hour week


    Hi Infomage,


    Hope you feel better.


    Sorry for the late response. Between work and the holiday season things have been crazy.


    This seemed to be far more complex than it should be so I called the Employment Standards agency for this area and have confirmed the following.


    Overtime is to be paid for hours worked daily in excess of 8 hours in a day or 44 hours in a week, whichever is greater.


    This means if the tech worked 10 hours per day for 5 days they would get 10 hours of OT. 2 hours per day x 5 days is 10 hours of OT. 50 hours for the week subtract 44 hours is 6 hours of OT. 10 hours is greater than 6 so the tech will get 10 hours of OT.


    Conversely if the tech worked 9 hours a day for 5 days and then 9 additional hours on Saturday they would get 10 hours of OT. Daily OT would only be 6 hours in total (1 hour per day x 6 days) where as the weekly total would equal 10 hours. 9 x 6=54 - 44 =10 hours OT.


    I hope this clarifies it. Sorry for the confusion on my part as well as the extra work.

    Re: Calculate overtime based on 8 hour day / 44 hour week


    Infomage,


    Thanks it works almost perfectly.


    In testing I found one small issue. If the tech works 8.25 hours on one day he'll get .25 hours of OT which is correct. If his balance for the week ends up being 44.25 hours the sheet is calculating that he will get paid an additional .25 hours while he was already paid the .25 hours on the day that he worked 8.25. The formula needs to look at the daily amounts paid to ensure that it's not double paying. I've attached an example.ozgrid.com/forum/core/index.php?attachment/70907/

    Re: Calculate overtime based on 8 hour day / 44 hour week


    Quote from Infomage;782494

    Let's make sure we're clear:


    * Hours >8 in a single day to count as overtime for that day
    * Hours >44 in any one week to count as overtime


    So, does that mean that someone who works 10 hours on Monday then 6 hours per day for the remainder of the week would have a total of 34 hours, 2 of which are overtime?


    Hello Infomage,


    Yes that is correct. Overtime is earned after 8 hours on any single day as well as when you surpass 44 hours total in one week.

    Hello,


    I have a working time sheet that we have been using for years and it functions very well. It is currently based on a 44 hour work week. I need to alter it so that it also takes into account the amount of hours worked in a day. It needs to calculate overtime after 8 hours in a day and when the cumulative time worked in the week exceeds 44 hours.


    For example,


    Joe Technician works 9 hours on Monday and then works 8 hours a day for the rest of the week. While he doesn't break the 44 hour threshold for the week he would still get paid 1 hour OT for the 9 hours he worked on the Monday. Conversely if he worked 10 hours a day for 5 days, by the 5th day he would get 4 hours of regular time and the remaining 6 would be paid as OT since he crossed the 44 hour threshold after 4 hours worked on the 5th day.


    I've included examples in the worksheet that shows how the current formula works. I would like to keep the current functionality and add in the 8 hour calculation.


    The formulas that I need to alter are located in cells E22:K22.


    Thank you in advance.


    ozgrid.com/forum/core/index.php?attachment/70898/

    Re: Alter all formulas based on cell value


    MrRedli,


    Thank you for the response. Reading up on the Indirect function, I'm not sure it's going to give me what I'm looking for. If I'm not mistaken (which I may be) I would have to go and alter every one of my existing formulas to include the indirect function. If this is the case then I really would like to avoid that as there are hundreds of formulas referencing over 100 employee names, so theres no way to quickly alter on a mass level.


    Here's an example of the formula.


    =SUMIFS('Jan'!$E:$E,'Jan'!$B:$B,"*Employee's Name*", 'Jan'!$D:$D,"<30000000")


    I'm hoping that there's a way to use the Replace function to change the 'Jan' in the formulas based on what's selected in the Data Validation drop down list. If I can't use a drop down list then I would be open to having the user input the name of the month manually. Doing it this way opens the door to errors if the month isn't typed in correctly.


    If this can be done via the Indirect function, I'd appreciate some guidance on how to make it work.

    Hello,


    I'm trying to find a way to alter or replace a text word (Sheet Name) contained in formulas throughout an entire worksheet.


    The workbook will contain multiple sheets. They are as follows; "Year to Date", "Period", "Jan", "Feb", "Mar" and so on for the rest of the year.


    I want to use a Data Validation drop down list, on the "Period" sheet containing all of the calendar months.


    I currently have multiple formulas within the "Period" sheet that reference one of the month sheets so that it can pull the data for that month from that sheet. Instead of having multiple "Period" sheets, I'd like to use a cell, lets say A1 for argument sake to use a Data Validation drop down list to pick which month I would like the "Period" sheet to reference.


    If the current formulas on the "Period" sheet are set to reference the "Jan" sheet, I would like the formula or VBA to find all instances of "Jan" in the formulas and change them to the month selected in the A1 drop down list.


    If "Feb" is chosen in A1, all instances of "Jan" in the formulas on that sheet would be replaced with "Feb" so all data on that sheet would reflect the period associated with February.


    I hope that makes sense.


    Thanks in advance.

    Re: Macro protection error 2003


    holycow,


    Thanks.


    I saw an example of that in a related thread where it would also add the password back into it as well. I'll go that way if I have to but I probably have 40 versions of this code in this workbook that I would have to go back and alter. I'd like to avoid that if there's any way possible.

    Hello to all,


    I have developed a survey sheet using 2007 and saved a copy for use with 2003. When end users try to utilize the protected sheet in 2003 they get a runtime error whenever they click the button linked to this macro. This also occured with 2010 version. I was able to load the version created with the 2007 onto a machine I have with 2010 on it and eliminated the error by unprotecting the sheet, re-protecting it making sure the Edit Objects box was checked. I tried this with 2003 on a machine running 2003 but I still receive the same error. I went as far as to check every box in the protection screen to no avail.


    The error goes away if you unprotect the sheet. Both the 2007 and 2010 versions work perfectly now. Can anybody help to determine if I'm simply unable to run this macro within a protected 2003 version?


    The code is to insert a picture



    Thanks in advance

    Re: Combining IF and SUMPRODUCT


    NBVC,


    It's amazing how the simplest of things can be the hardest to see sometimes. I just had to add the Contact! into your formula and it worked perfectly. I probably should have stepped away, had a drink, watched a mindless show and it would have came to me. Thanks for the quick and accurate response.

    Hello to all,


    I'm trying to incorporate an IF into this formula based on the value of cells on another sheet within the same workbook.


    =SUMPRODUCT(((D$2:D$2999)-E$2:E$2999)*(G$2:G$2999>=N2)*(G$2:G$2999<=O2)) This formula works perfectly.


    D2:D2999 are values of jobs. E2:E2999 is the value of products returned from the job. G2:2999 are completion dates. N2 & O2 are Month start and end dates respectively.


    I want to be able to run this formula based on the value in cells L2:L2999 on a separate sheet titled Contact.


    Basically if the value in Contact! L$2:L$2999 equals CA then I want to total all of the corresponding values in D & E 2:2999. by incorporating the SUMPRODUCT formula above. If not then I want the cell to remain blank.


    I've tried =IF(L$2:L$2999="CA",(SUMPRODUCT(((D$2:D$2999)-E$2:E$2999)*(G$2:G$2999>=N2)*(G$2:G$2999<=O2))),("")) but it doesn't work.


    Thank-you in advance for your assistance.