#### Ironman

##### Well-known Member

- Joined
- Jan 31, 2004

- Messages
- 1,069

- Office Version
- 365

- Platform
- Windows

Hi

Row 369 contains years - AN369 is 2020, AO369 is 2021, AP369 is 2022 and so on, up to 2061.

Col AO372 contains a formula value for the current year, currently 2021.

Col AN372 contains a formula value for the previous year, currently 2020.

When the value in AO372 (this year) exceeds AN372 (last year) by less than 11 I need a simple message box "this year's total is greater than last year".

I also need the column to auto update each year, so on Jan 1 2022 the 2 cells compared will be AP372 (for 2022) and AO372 (for 2021) and so on each new year.

Hope you can help?

Many thanks!

Row 369 contains years - AN369 is 2020, AO369 is 2021, AP369 is 2022 and so on, up to 2061.

Col AO372 contains a formula value for the current year, currently 2021.

Col AN372 contains a formula value for the previous year, currently 2020.

Cell Formulas | ||
---|---|---|

Range | Formula | |

AN372:AO372 | AN372 | = SUMPRODUCT(--($A2:$A367<=YEP_LastDate),AN2:AN367) |

Named Ranges | ||
---|---|---|

Name | Refers To | Cells |

EntRng | =OFFSET('Daily Tracking'!$Y$2:$Y$367,0,YEAR(TODAY())-2005) | AO372 |

Cells with Conditional Formatting | ||||
---|---|---|---|---|

Cell | Condition | Cell Format | Stop If True | |

AO372 | Expression | =AO372>=LARGE($C$375:$CC$375,1) | text | YES |

AO372 | Expression | =AO372>=LARGE($C$375:$CC$375,2) | text | YES |

AO372 | Expression | =AO372>=LARGE($C$375:$CC$375,3) | text | YES |

AN372 | Expression | =AN372>=LARGE($C$375:$CC$375,1) | text | YES |

AN372 | Expression | =AN372>=LARGE($C$375:$CC$375,2) | text | YES |

AN372 | Expression | =AN372>=LARGE($C$375:$CC$375,3) | text | YES |

B372:AE372,AP372:CC372 | Expression | =B372>=LARGE($C$375:$CC$375,1) | text | YES |

B372:AE372,AP372:CC372 | Expression | =B372>=LARGE($C$375:$CC$375,2) | text | YES |

B372:AE372,AP372:CC372 | Expression | =B372>=LARGE($C$375:$CC$375,3) | text | YES |

When the value in AO372 (this year) exceeds AN372 (last year) by less than 11 I need a simple message box "this year's total is greater than last year".

I also need the column to auto update each year, so on Jan 1 2022 the 2 cells compared will be AP372 (for 2022) and AO372 (for 2021) and so on each new year.

Hope you can help?

Many thanks!

Last edited: