Posts by myexcelstar

Dear Ali

my file contains exactly same data as my real one (except far less records). Employee ID 235 is listed in two rows (sorry, one has wrong name, but this shouldn't matter as the formula only looks at the ID), Row 1 & Row 2. But the formula only displays the leave from row 1. The second and any additional row of the same person would not be displayed in the calendar. So if I have an employee with 5 rows (meaning 5 vacation requests), only the first one will be populated. How can I get the additional ones to display?

Thanks again for your time & assistance.

Dear Ali
I tested your formula and it is working fine.... except if there are several entries on the report file for the same person. Example, employee 235 has two entries, but only the first one is shown in my calendar, the second one in August does not show. My real file is exactly the same, but can have several entries per year. Any way to show all those entries with your formula??

Thanks so much for your help.

Dear AliGW
thank you for your suggestion. Unfortunately I was not yet able to test it on my real file as I have been out of the office. I will check later this week and update you. I truly appreciate your help.

Dear Chirayuw

thank you for your suggestion, unfortunately that won't work. What I want is the calendar read the dates out of the report and place the x's in the respective days. The report is given from the system and I don't want to modify or format it in anyway. I want Excel to read the info out of it.

Good morning,
I was hoping to get some guidance here for a Leave calendar I want to create. I have the calendar set up with dates across and the names/IDs down (see attached sample). What I want to do now is, use a report I am getting from the system that shows the start & end date of the leave of a person/ID and have this information populate the calendar with an "x" on the days that fall within the requested leave dates from the report.

My problem is not getting the individual days populated with the "x" (using >= and<=), what the problem is,
a) on the report, someone can have multiple requests for leave (eg. 1st May - 5th May and another request from 7th June 15th June), how can I capture all requests of a person (with same ID) and have all requests populated in the calendar
b) how do I find the values in the report (based on the ID on the calendar) considering that someone can have multiple requests in the report (so vlookup is not an option)

Hope this is not too confusing.

Thanks so much for your time and help in the little project.

Re: How to clear contents in range of cells if older than a year (or more)

Hi cytop, thanks so much for your script, I really like it, however I seem to have some problems:
- I think I miscommunicated what I was trying to achieve. I was hoping to be able to have the script delete dates in column D that are older than 2 years only, in column E dates that are older than 3 years and in column F those that are older than 5 years.
- I input a few dates in 2011 that are older than two years (1/4/2011, 1/1/2011) but it won't delete those?! It starts deleting from 12/31/10... does it only look after the year, not from today's date on?
- Also, if one column is empty, it gives me an error message and wants to "debug", not all my columns will be filled at all times.
- Can I determine that the script only looks between certain rows (eg. between row 4 - row 658)? How does it determine the last row to be checked?

Re: How to clear contents in range of cells if older than a year (or more)

Hi cytop, thanks so much, this is working well,.... now I just need the code to actually clear the cells.

[INDENT]Hi there,
I have a spread sheet with certification listings of staff. Those expire after a certain amount of years. How can I set up my sheet that it deletes (clears the cells) any expired certification dates?
Example: Certificate A expires every 2 years
Certificate B expires every 3 years
Certificate C expires every 5 years
What I would like to happen is, that upon opening of the file a dialog box comes up and asks: "Would you like to delete old Certificate data?" along with a "YES" and "CANCEL" button. If I click "YES", it deletes all dates in column D, E & F that are older than 2, 3 and 5 years. The formatting should NEVER change in those columns though (so I guess it is ClearContents?!). The dialogue box would come up every time the worksheet is being opened.
Can this be done???
Thanks so much for your time & help[/INDENT]

Re: Macro to delete range in specific row with input box

Hi Pike, will do another search. Thx for your help.

Re: Macro to delete range in specific row with input box

Pike, yes I did. But nothing that was suitable for me, that's why I posted it here.

Hi
I am hoping someone out there can help me. I need a macro, that let's me clear a range of cells in a specific row. The input form should ask me for the row number to clear and it would clear a specific range (that range would always be the same).

I have attached a sample file. In this file, I would like to attach a macro to the inserted symbol. Once I click it, it will bring up an input box that asks: "Enter row number you would like to clear". It should have an OK and a CANCEL button. Once you click OK, it should ask: "Are you sure you want to delete row XX (showing the number you entered in the first step)? Again, an OK and CANCEL button. Once you click OK here, it clears the fixed range. In this example, the range to be deleted should always be column D - column I.
So for example, if I enter "3" in the input box, it would clear all cells between D3 - I3.
To push it a little further, is it possible for the cursor to jump to D3 and highlight D3 - I3 and once you click OK on the second message ("are you sure...."), the highlighted selection is removed again?

I hope this makes sense.

Re: Macro for vacation accrual sheet

Thanks so much cytop, I do apologize if I didn't use the correct way of posting the code. Thanks also for your suggestion which works great, but my sheet is very wide and also a few hundreds of rows long. So marking those manually is a lot of work. That's why I was hoping I can input the range in a userform or so before running the macro from that userform. Is that possible? Or maybe defining the range in two cells (eg. A1 contains the first value of the range like A3, and cell B1 contains the last value of the range like BL899). And the code would pick up those values from cells A1 and B1.

Thanks again for your great help!!

Re: Macro for vacation accrual sheet

OK, after some research I found a VBA code I think I can use if there is one thing that can be added. The code I found is this?

Sub NoCopyAndPaste()
     Sheet1.Range("A1:A10").Value = Sheet1.Range("A1:A10").Value
End Sub

[Is there a way to manually input the range (........:.....) before I run the macro, using something like a userform or so??? I don't want to overwrite the formulas in the future, just in the past, so I want to enter the range to copy & paste manually?!

Re: Macro for vacation accrual sheet

Good morning everyone, was just wondering if anyone had any solutions for me?! Thanks so much

I am working on a vacation accrual sheet & need some help please:
Every pay period, the monthly accrual is logged in columns labeled “A”. The accrual is done by placing an “x” in row 2 of each period (eg. O3, Q3, S3, etc). This will populate the entire column with a number that corresponds with the length of service period defined in L, M, N. So, if the person has been in the company for 1-4 years = 1, 5-9 years = 2 and for 10+ years = 3. When someone moves from one category (L, M, N) into the next, it sets all previous accrual fields to that number. For example: moving from the 1-4 yrs category (which would show a “1” in O, Q, S, etc. into the next one of 5-9 yrs (which should be a “2” in the accrual colums) then all previous “1”s end up as a 2.
I need a solution that copies the current accrual column onto itself with its values only. I created a Macro for that but the problem with this is that I have to create about 100 of those, one for each “A” column.forum.ozgrid.com/index.php?attachment/44796/

Re: Userform to enter data in cell of protected sheet

Thank you so much for your suggestions. I figured out another way, I just created another unlocked cell to enter the password (I formatted it "*""*""*""*""*""*" to display only *) and I use the vlookup to assign the names to display on the signature line.

Good morning

I am trying to create a sheet in which I can insert an "electronic" signature. Please see attachment for this report.

What I would like to achieve is, if someone clicks on cell A7 for the signature, a userform opens up and the person has to enter his/her code (column M) and then the corresponding name (column N) will be inserted and displayed in A7. For example, if I want John Smith to "sign" this form, he has to click on A7, userform appears, he enters code 2356 and then his name will appear as a "signature" in A7.

The sheet has more fields to enter data in, but those should be entered manually, without userform. Only A7 should have the userform to enter data.

Can this be done????? During this entire process, I want to keep this sheet protected at all times.

Thanks so much!

Re: Turnover Report

Anyone has a solution to my problem?

Good afternoon,

I am trying to create a turnover report in which I deduct workers from one company and add them to the next one (see attachment).
For example: If my worker moves in a specific month (eg. April, the month is determined by 'Sheet1' cell F1) from company 1 to company 2, then I want on 'Sheet1' the value -1 in cell A4 and a +1 in cell B4, etc. So I need a formula that looks for all workers in April who moved from one company to the next one and deduct those numbers from the particular cell in 'sheet1' (A4, B4, C4 or D4) and add it to the new company's cell in 'sheet1'.
I hope this makes sense and that someone can help me. I tried the COUNTIF function, but that doesn't work with the month variable in it. And I couldn't get the SUMPRODUCT to work either.

Thank you so much for your help!!

Hi
I am coming here again because this is a great forum with fantastic people who always have a solution for a problem. I have another challenge. I am creating a turnover report and I need to count those people who left based on their length of service (see attached file). What I need is:

In B15 I need a formula that counts all people with CODE10 who have been there less than 30 days. In B16 I need a formula that counts all people with CODE10 who have been there between 31 - 90 days, etc. In C15, 16, ... accordingly people with CODE25, etc.

The problem is, where can I get this information from? B2:B9 and C2:C9? Or from D2:D9? Do I have to convert the difference between the Hire Date & Term Date to days first? Or is there a formula that does all that? My formula needs in the end use two conditions, the amount of day and the code.
How would this formula change if I needed to put another condition in it? eg. the location where those people worked? eg. in column F2:F9?
Anyone who can help me? I appreciate your time to look into this. Thanks so much!!