Solved

excel move automatically expired dates (row) to second sheet

Posted on 2013-01-24
7
1,590 Views
Last Modified: 2013-05-05
Hi, I'm trying to get rows in my spreadsheet to move to another sheet once the dates expired.   Here is an example row of a date expired.  I want this yanked automatically and place on sheet two.  Can you help?  please...

ALL ABOUT PRESERVATION      12/9/2012      1/18/2013
0
Comment
Question by:snoopaloop
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
Comment Utility
If you want to get the row to actually move, I think you need to have VBA code involved.

You could create two spreadsheets, and filter them both by  >now() or <now() depending on the sheet.  That might work if now is allowed as a filter.

That could have the effect of making them look like they move.  

-SA
0
 
LVL 11

Expert Comment

by:ScriptAddict
Comment Utility
Hmmm doesn't look like that will work,  I guess I would use conditional formatting.  

That should be able to color a cell based on the date compared to today, and then just filter the color that has expired instead of based on date.  

If you are using Excel 2010 that should work.  If you have to do it with VBA that is going to be a different approach.
0
 
LVL 1

Author Comment

by:snoopaloop
Comment Utility
Im not sure what that means can you explain??
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Expert Comment

by:ScriptAddict
Comment Utility
what version of Excel are you using?
0
 
LVL 1

Author Comment

by:snoopaloop
Comment Utility
Excel 2010
0
 
LVL 11

Accepted Solution

by:
ScriptAddict earned 500 total points
Comment Utility
Ok,

Here is how this solution would work.

1) Take your spreadsheet and copy it to a second spreadsheet.  So you get a second tab.

2) Put Auto Filters on both sheets.

3) Select column C (assuming that's where the date you care about is)

4)Select Home>Conditional Formatting > new rule > Use formula to determine what cells to format.

5) Enter something like this =(C1-TODAY())<=0 as the formula.

6) have the rule apply to C:C assuming C is the date column you are interested in.

7) Select a color (I chose the normal highlight color) for items that match.

8) Save the rule and make sure that your dates are entered as dates and not text by re-keying a single one of them if the rule isn't working right.

9)Turn on the filter on both sheets

10)On the first sheet filter by color on the column you are interested in (highlighted color).  And on the second one filter by color on the column but you select no fill.

This gives the illusion that they are moving, but no VBA coding is required.  You'll need to update the second sheet from the first each time you update the worksheet, or periodically, by copying it and setting the filter on the color or no fill.

However it should be functional with no VBA coding required.

-SA
0
 
LVL 1

Author Comment

by:snoopaloop
Comment Utility
THere's a fix that the other IT consultant used to resolve it.  I would get that info tomorrow.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now