Ready, steady… save!
You don’t need a chart to join in. Here are 2 ways to record your savings:
1. Cross it off
Pop a chart into your first post. Saved an amount? Cross it out! Read more »
£0.01 £0.02 £0.03
2. Write it down
Skip the chart and add updates to your first post. Read more »
“Another +£3 saved today. Go me!”
Use +£ before each new saving so it counts towards your total.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Mindboggling Excel Stuff!
Wings_of_Ambition
Posts: 1,747
Forumite
in Techie Stuff
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.
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.
0
Comments
-
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.
SJB0 -
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.0 -
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.0
-
He's still here, with a different name. He'll probably turn up laterLoobeylou 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.
Bulletproof0 -
Different name - being what?0
-
Couldn't possibly say - it's 'inside' information..0
This discussion has been closed.
Confirm your email address to Create Threads and Reply
Categories
- All Categories
- 355.9K Banking & Borrowing
- 255K Reduce Debt & Boost Income
- 456.2K Spending & Discounts
- 248.5K Work, Benefits & Business
- 606.2K Mortgages, Homes & Bills
- 179.1K Life & Family
- 264K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.2K Discuss & Feedback
- 37.7K Read-Only Boards