Afternoon all,
I am having a bit of a dumb moment with Excel.. I can't seem to think of a way to do what I need to, can someone please help?

Basically I have two worksheets in one workbook. One is called summary and the other is called data input.

Summary Worksheet
Column A - has a list of areas
Column B - titled "Done in last 12 months"
Column C - titled "Next due by"


Data Input Worksheet
Column A - has a list of areas
Column B - titled 2012
Column C - titled 2011
Column D - titled 2010
etc etc


Basically, users input the date an audit has been completed in the respective year column against the area.

What I am trying to work out is the best way to do the following:

Column B [Summary] - to show yes/no (maybe some visual conditional formatting too) depending if there is an appropriate date in the year columns [Data Input] that is less than 365 days from todays date

Column C [Summary] - show when the next due date is (again maybe with some visual conditional formatting thrown in e.g. if well overdue or if coming up)

The part I am mainly struggling with is how to search the columns and get Column B [Summary] to show if there was a date within the last 12 months. Plus, the fact that this will grow over the years so each year Column B [Data Input] will be the current year.

Anyone have any suggestions on how to tackle this?


Thanking you in advance

mads

Have a cell which displays "=MAX(B:B)" for the dates you want to search.
This will bring up the latest date.

Then use this on that cell:

=IF(C5>NOW()-365, TRUE, FALSE)

That will compare the last date in the list again today's date minus 365 days.