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
Bookmarks