Mindboggling Excel Stuff!

Wings_of_Ambition

Ok, my mind is boggling with Excel functions, so its over to the Techies! I want to achieve the following - which functions do I need to use.

I have a list of names in one column, and a list of dates in the next.

I want to effectively filter the table so that for each date that is futher back than "today minus one year", that date and the associated name appear on a shorlist on a seperate worksheet, preferable in date order. It would also be useful if I could keep the original two columns in alphabetical order of names. I would like it so that once the date has been updated (so that it should no longer be flagged up), it automatically disappears from the shortlist.

So its over to you! Your suggestions much appreciated.

StephenB_2

Assume sheet1, column A contains the names and sheet1 column B contains the date they last paid.

set sheet2 cell A1 to: =IF(TODAY()-Sheet1!B1>365, Sheet1!A1, "")

set sheet 2 cell B1 to: =IF(TODAY()-Sheet1!B1>365, TODAY()-Sheet1!B1, "")

Drag and copy a1:b1 down a suitable number of times. At this stage the names and the number of days since payment will be displayed for all the slow payers. They will not be in any order.

Highlight columns A and B. Select data, sort. Choose "sort by (Column ", "descending", and check "no header row" at "My list has".

You should now have what you need.

SJB

spotter

Try the Advanced Filter (Data > Filter > Advanced Filter).

The List range needs to include the column headings (e.g. Name and Date).
The Criteria range should include the column heading (e.g. Date). Use '="<" & TODAY()-365' as the Criteria, this will display as '<38366' for 14/1/2005.
For Action select 'Copy to another location'. If copying to another sheet, before starting Advanced Filter make the destination sheet active.

To Sort the results use Data > Sort and sort by Date.

Once you are happy with the results, just record a macro (Tools > Macro > Record New Macro) of the process above.

Loobeylou

Anyone know what happened to Mr Skint? He was really good at this kind of problem, but he seeemed to disappear with an "I'm off" message several weeks ago, and I have not seen him on this site since.

tru

Loobeylou wrote:

Anyone know what happened to Mr Skint? He was really good at this kind of problem, but he seeemed to disappear with an "I'm off" message several weeks ago, and I have not seen him on this site since.

He's still here, with a different name. He'll probably turn up later

Loobeylou

Different name - being what?

ManAtHome

Couldn't possibly say - it's 'inside' information..