Need to delete expired dates from a list of employees

**mrgillus** · 08-03-2009, 08:28 AM

Basically I have a list of employees. This list is simple in that it shows their off time "From:", "Till:". in columns to the left of there name. So in Cell A:A would list all of there names.. In the example sheet I have just a few listed. To the left of their name if "From:" (column B) and then "Till:" (Column C). And this From, Till repeats itself for several columns so that multiple off-times may be made for each employee.

Basically what I want to do is delete any sets of dates that are expired (based on the "Till:" date) and shift the remaining dates over. So in other words, if Sam has a date in there of 8/1/09 to 8/1/09 meaning he is going to be off for just that day, and today's date is 8/2/09, then that "from" and "till" date of 8/1/09 to 8/1/09 would get deleted from his list and the remaining dates (if any) would get shifted to the left.

Hopefully I have provided a good enough explanation of what I am trying to do here.. Please let me know if you have any questions! I have provided an example the reflects my setup (and the setup can be changed if it makes anything easier)

Thanks for all the help!
p.s. this is done with Excel 2007

**JBeaucaire** · 08-03-2009, 10:42 AM

Try this:

Please Login or Register  to view this content.

**mrgillus** · 08-03-2009, 10:53 AM

Wow that works great! I was actually trying to assemble some code myself but was definitely very primitive compared to what you have.. I will study this and hopefully learn something from it!

I greatly appreciate the help!

**JBeaucaire** · 08-03-2009, 11:07 AM

Commented code to help explain:

Please Login or Register  to view this content.

Also, I made a correction in the line of code in red, so update that!

**mrgillus** · 08-03-2009, 11:36 AM

Thanks Sir for your help..

I've updated the code and now have an additional question.. Well actually I just added this in as it makes the from much easier for the user to follow. How can this code be written so that my conditional formatting also gets replaced. And, There is another sheet in the real version that references to the dates in Columns "b" and "c". But it loses its information and displays "ref#" or something like that when I run this code.. Anyway to fix this? Sorry deeply as I should have mentioned that in the example but I didn't think it would try and update the reference on the other sheet

The newly attached example sheet demonstrates the problem...

Thanks again for all your assistance

**mrgillus** · 08-03-2009, 11:37 AM

Please use the example here as I made some changes and could not seem to get it to change on the previous post.

**JBeaucaire** · 08-03-2009, 11:50 AM

1) Updated code to include your CF (created by simply using the Macro recorder)

Please Login or Register  to view this content.

2) Change the formula on page "Refer" to this format:

=INDIRECT("Sheet1!B3")

**mrgillus** · 08-03-2009, 12:04 PM

Once again, Works like a charm! One final question regarding Indirect..

Is there anyway to autofill that series? If I can avoid having to change the "C3,C4, etc. from that =INDIRECT("Sheet1!C3") in every cell on my "refer" sheet.. On my actual sheet there is more like 40-50 names listed..

If not I will run through it as you have saved me a lot of time already!

Thanks

**JBeaucaire** · 08-03-2009, 02:29 PM

In A1, enter this formula and just copy down as far as you wish:
=INDIRECT("Sheet1!B" & ROW() + 2)

In B1:
=INDIRECT("Sheet1!C" & ROW() + 2)

**mrgillus** · 08-04-2009, 10:37 AM

Thanks JB for your help.. This works perfect!

Need to delete expired dates from a list of employees

LinkBack

Thread Tools

Rate This Thread

Display

Need to delete expired dates from a list of employees

Re: Need to delete expired dates from a list of employees

Re: Need to delete expired dates from a list of employees

Re: Need to delete expired dates from a list of employees

Re: Need to delete expired dates from a list of employees

Re: Need to delete expired dates from a list of employees

Re: Need to delete expired dates from a list of employees

Re: Need to delete expired dates from a list of employees

Re: Need to delete expired dates from a list of employees

Re: Need to delete expired dates from a list of employees

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions