Hi all,
I am slowly getting through a spreadsheet I am trying to make to show requirements for training, date taken, then highlight when it becomes due, or overdue. This will then allow you to email a copy of the sheet, or drop a graph straight into powerpoint for you.
Most of it is done now, I am struggling with the final formulas for the graph.
The formula needs to count any cell from a range the first worksheet (Current), and any that is empty, or have a date of earlier than today based on the criteria of a vlookup table, then it should count it. The same would be for today+30 days and today+90 days. (which then all go into the graph to show training either overdue, or due in 30 days or 90 days.
The formula I thought was based on the conditional formatting rules
<code style="font-family: monospace; font-style: inherit; font-weight: inherit; line-height: 100%; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">=counts(Current!G8:M59<>"",TODAY()>Current!G8:M55+VLOOKUP(Current!G$5,dayslookup,2,0)-30)</code>
but obviously doesn't work
I am not sure how to attach the sheet for you to see though
Any help appreciated please.
EDIT
Also posted here
https://www.excelforum.com/excel-ge...rmula-based-on-content-of-a-vlookup-list.html
I am slowly getting through a spreadsheet I am trying to make to show requirements for training, date taken, then highlight when it becomes due, or overdue. This will then allow you to email a copy of the sheet, or drop a graph straight into powerpoint for you.
Most of it is done now, I am struggling with the final formulas for the graph.
The formula needs to count any cell from a range the first worksheet (Current), and any that is empty, or have a date of earlier than today based on the criteria of a vlookup table, then it should count it. The same would be for today+30 days and today+90 days. (which then all go into the graph to show training either overdue, or due in 30 days or 90 days.
The formula I thought was based on the conditional formatting rules
<code style="font-family: monospace; font-style: inherit; font-weight: inherit; line-height: 100%; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">=counts(Current!G8:M59<>"",TODAY()>Current!G8:M55+VLOOKUP(Current!G$5,dayslookup,2,0)-30)</code>
but obviously doesn't work
I am not sure how to attach the sheet for you to see though
Any help appreciated please.
EDIT
Also posted here
https://www.excelforum.com/excel-ge...rmula-based-on-content-of-a-vlookup-list.html
Last edited by a moderator:
