Posts by Excel Noob


    I have a large spreadsheet that contains customer information in each row. There are more than 500 columns to the right that contain part numbers and descriptions in the top row. I need to be able to look at a row and return some customer data such as customer name currently contained in A2 and store number in A3 along with every column in that row that contains a numerical value from column AD on and it's associated column title contained in AD1 and on respectfully.

    I attempted to use a pivot table but it's just too unwieldy as it shows everything even if it's blank. I just want to show the columns that have a value in them.

    Not sure this is even possible. Any suggestions?

    Hello All,

    I have a Macro that is largely doing what I need, but I would like to make the following changes to it.

    1. I would like the PDF to be saved in the same location as the Excel file it was created from. Currently it is asking me to select a location.
    2. I want to have it create a PDF of either the active worksheet or both the active and second worksheet based on the value in one specific cell. Currently it is only converting the active worksheet with no reference to any cell.
      • If cell I16 equals either "New Install" or "Install" then the active sheet is converted along with worksheet 2 which is titled "Install Travel Lot" and emailed. The current macro names the PDF as the active worksheet which will always be worksheet 1 as that's where I have placed my Submit Button. I would like this to remain in place regardless of whether it's converting worksheet 2 or not.
      • If cell I16 equals anything else, including remaining blank then only the active worksheet is converted and emailed. I16 currently contains a Data Validation List just in case that makes any difference
    3. The PDF is being named after the active worksheet. I would like the PDF to be named after the file name instead

    Here is the current macro I have. It is fully functional with the exception of my wish list above. Thank you in advance for any help I may receive on this!

    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.


    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.


    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,


    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


    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

    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.


    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.

    Re: Alter all formulas based on cell value


    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.


    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



    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.