If you use the formula as you wrote
=IF(MAX(INDIRECT(Sheet2&"!B5:B200))(TODAY()-21),"not due","due")
Excel is looking at the Indirect function and looking for a Name Sheet2
which does not exist therefore #REF
It needs to be text so
=IF(MAX(INDIRECT("Sheet2"&"!B5:B200))(TODAY()-21),"not
due","due")
would work
However this will not increment to Sheet3 as you copy down
The solution is to create the list in one column
A1 Sheet2
A2 Sheet3
A4 Sheet4 etc..
which can be done by filling down
then use the function to reference the cells containing the sheet name
=IF(MAX(INDIRECT(A1&"!B5:B200"))(TODAY()-21),"not due","due")
=IF(MAX(INDIRECT(A2&"!B5:B200"))(TODAY()-21),"not due","due")

Hope this clarifies

RES