Hi

I have a spreadsheet that has a rolling calendar that automatically updates to show todays date and the last 365 days when opened using a macro
The calendar dates starts at cell A18 through to A383

I have the below formula looking for "Yes" in column F over the last 182 days to calculate a trigger point, but the cell reference changes everyday by one, so if I open the spreadsheet tomorrow the cell refeence will start A19:A384 and F19:F384 which I dont want it to do.

=SUMPRODUCT(--(A18:A383>=TODAY()-182),--(F18:F383="YES"))

I tried the $ with the cell references but this didnt work and I sumbled across the INDIRECT function and thought this may be a solution ?

Would INDIRECT stop the cell reference from moving and if so how would I combine it with my above formula ?

Many thanks for your help in advance
CAB