IF(COUNTA(OFFSET formula arror

[Chunky_22]

Hi guys

This may be too hard to explain here, but I'll give it a go. My sheet is too large and complicated to post. And it was built by someone else, I dont fully understand it.

Cell B3 is a named range - 'CurrMthCellCount". It contains a formula:
=MATCH(CurrentDate,5:5,0)

CurrentDate is selcted by the user.

Row 5 is a date range, from 01/05/2007 in E5 all the way to 01/07/2011 in BC5. There is data under this down to row 77, with headers down column A


There is another formula in B4, and this is the one with the error:
=IF(COUNTA(OFFSET(A6:A77,0,CurrentMonthColumn-1))=0,"FALSE","TRUE")

This formula works fine until the user selects 01/06/2010 or later. 01/05/2011 or earlier returns a TRUE result. 01/06/2011 or later returns a FALSE result.

This FALSE result prevents some macros from running.

Any ideas?

 

[Greg Truby]

Sounds to me like whoever built this, did it as a safety measure. It's a simple check to see if there are any cells with data below the header that matches the selected date. My guess is that the macro will either error or not need to be run if there is no data below the matching date, hence the check. My suggestion would be to investigate further.