Posts by GoCavs

    I have multiple workbooks (20+) located in a same file path folder that I would like add sheet protection to. Each workbook has only 1 sheet (sheet1) in it. Is there a way for a macro to run on each of the files in this folder and add worksheet protection to them rather that doing it manually? I would like the password for the sheet protection = md


    I only need "Select Locked Cells" and "Select Unlocked Cells" checked for the properties.


    Is this something that is doable?


    Thanks!

    Re: $35 USD: Macro to auto-fit merged cells and hide blank rows


    Thanks Kris! Looks good but I have one question. For some reason row 26 (Total row) on 2014 Performance tab seems to getting hidden even though it doesn't have "Hide" in column M. Everything else seems to working OK. Would you be able to look at that?

    I am looking for a macro that will do some formatting clean up to some tabs in a excel file used for annual review feedback. The tabs contain rows with merged cells (I realize that that these should be avoided but I inherited the file and trying to help out) and much of the data is copied and pasted into cells from other sources (word docs, e-mails, other excel files). During the annual review process there are over 100 of these files that are completed and administrators need to review and clean-up or fix any formatting issues so it looks good for printing purposes. I am looking for a macro that the admin can run to do the clean-up formatting that will accomplish the following 2 tasks on tabs '2014 Performance' and 'RA Development Feedback':

    1) Auto-Fit Row Height for rows with Merged Cells: One of the issues is that rows with merged cells do not auto-fit the row height. Some of the data that is being pasted into the rows with merged cells has more text in the cells than is visible due the row height not adjusting height. Admins manually adjust these rows to show all of the text in the rows and also reduce some row's height for rows with "extra space". Could a macro look for rows with merged cells in the 2 tabs and then auto-fit the row height?


    2) Hide Blank Rows based on Criteria: The other main issue is that there may be rows that have no data and I would like to have the macro to hide these rows. In column M, I have a formula that looks for rows that are blank and that should be "hidden" by the macro. So any cell in Col M that equals "Hide" should be hidden. Would like to add this to the macro as well.


    The attached file is a sample.


    Pre-payment has been sent. Thank you in advance for your help.

    I am looking for formula that will calculate the Date Value in the column header for the last value (going left to right) NOT EQUAL to 0 in a row. For example, in the attached sample file, I have Date Values in row 1 (C1:V1) which range from Jan-13 to Aug-14. The data below shows the revenue generated per Client by month. For each row (Client), I am trying to calculate when the last month when any revenue was generated and put the value in column W. The attached example should provide a good sample of what I am trying to do. Thank you in advance and let me know if you have any ?'s.


    Ozgrid Pre-payment has been sent.




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

    [h=2]Hide Columns based on Date value in cell (Worksheet Change event)[/h][INDENT]Looking for a worksheet_change event macro that will automatically hide columns based on a value in cell S3. S3 is a data validation pick list of months based on the 1st day of each (e.g. 1/1/2014, 2/1/2014, 3/1/2014, etc.). I have date columns in row 6 from columns T through AQ. These values are 1/1/2014 (T6), 2/1/2104 (U6), 3/1/2014 (V6)....12/1/2015 (AQ6). I would like to hide columns that have a date in row 6 (T6:AQ6) that is LESS THAN (<) the date in S3 after the cell is value is changed.


    Hope this makes sense. Let me know if you have any ?'s.


    Thank you!!![/INDENT]

    Re: USD $30.00: Sum Year and Month values


    I would prefer to have the calculation based off of the actual Years and Months column. The Yrs/Mons column I created myself as alternative style for show.


    As of now, these fixed categories. But I would expect more columns/categories in the Pre-Hire experience section in the future.


    Thank you!

    Working on a file used for tracking consultant compensation and includes a section where HR will input how many Years and Months of experience and employee has in certain areas. The Year and Month columns for each category are separate and are number values (e.g. Years of Experience = 4 and Month of Experience = a number between 0-12). I am looking for a way to sum up these individual Year and Month columns that contain the number values to calculate a Total Years of Experience per employee.


    For example:


    [TABLE="width: 929"]

    [tr]


    [td][/td]


    [TD="colspan: 3, align: center"]sum of all experience[/TD]
    [TD="colspan: 3, align: center"]Post-Hire Experience[/TD]
    [TD="align: center"][/TD]
    [TD="colspan: 9, align: center"]Pre-Hire Experience[/TD]

    [/tr]


    [tr]


    [TD="align: center"]Name[/TD]
    [TD="colspan: 3, align: center"]Total years of experience[/TD]
    [TD="colspan: 3, align: center"]Consulting / Directly Related[/TD]
    [TD="align: center"] [/TD]
    [TD="colspan: 3, align: center"]Consulting / Directly Related[/TD]
    [TD="colspan: 3, align: center"]Other Industry [/TD]
    [TD="colspan: 3, align: center"]Career Gap/ Intern[/TD]

    [/tr]


    [tr]


    [TD="align: center"]Years[/TD]
    [TD="align: center"]Months[/TD]
    [TD="align: center"](Yrs/Mos)[/TD]
    [TD="align: center"]Years[/TD]
    [TD="align: center"]Months[/TD]
    [TD="align: center"](Yrs/Mos)[/TD]
    [TD="align: center"]Hire Date[/TD]
    [TD="align: center"]Years[/TD]
    [TD="align: center"]Months[/TD]
    [TD="align: center"](Yrs/Mos)[/TD]
    [TD="align: center"]Years[/TD]
    [TD="align: center"]Months[/TD]
    [TD="align: center"](Yrs/Mos)[/TD]
    [TD="align: center"]Years[/TD]
    [TD="align: center"]Months[/TD]
    [TD="align: center"](Yrs/Mos)[/TD]

    [/tr]


    [tr]


    [TD="align: center"]Name, 1[/TD]
    [TD="align: center"] To Be Calc[/TD]
    [TD="align: center"] To Be Calc[/TD]
    [TD="align: center"]To Be Calc[/TD]
    [TD="align: center"]1[/TD]
    [TD="align: center"]2[/TD]
    [TD="align: center"]1.2[/TD]
    [TD="align: center"]2/1/13[/TD]
    [TD="align: center"]6[/TD]
    [TD="align: center"]1[/TD]
    [TD="align: center"]6.1[/TD]
    [TD="align: center"]9[/TD]
    [TD="align: center"]0[/TD]
    [TD="align: center"]9.0[/TD]
    [TD="align: center"]0[/TD]
    [TD="align: center"]0[/TD]
    [TD="align: center"]0.0[/TD]

    [/tr]


    [/TABLE]


    I attached a sample file for reference. If there is a better way to accomplish this, please feel free to provide an alternate solution. Let me know if you have any ?'s. Thank you!


    10% pre-payment has been sent