Hi JG,
You might help answer your own question, if you tried the formula,
or split it up into components to see what it does.
..
You use the fill handle to copy the formula down
. http://www.mvps.org/dmcritchie/excel/fillhand.htm
Use of INDIRECT will use the address of the value returned
as opposed to the value -- indirect reference
http://www.mvps.org/dmcritchie/excel/indirect.htm

=IF(MAX(INDIRECT("Sheet"&ROW(B2)&"!B$5:B$200"))(T ODAY()-21),"not due","due")

ROW(B2) returns 2 because that is the row that B2 is on
ROW() if it were used would return the row that the formula is on

you might be able to see what is happening if you test with just this part in a cell
it could be on any row because it is B2 that indicates the row used in the
formula to be used to complete the sheet number2
="Sheet"&ROW(B2)&"!B$5:B$200"
which will give you a clearer idea of the what the full formula is doing

Sheet2!B$5:B$200
Sheet3!B$5:B$200
Sheet4!B$5:B$200
Sheet5!B$5:B$200
Sheet6!B$5:B$200

Which is like what you asked for, those are sheet numbers and not sheetnames.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JG" wrote in message ...
Greg, thanks for your suggestion, but please excuse my ingnorance, does the
text "Sheet" need to be replaced by sheet1, sheet2 etc.

"Greg" wrote:

Assuming you want to refer to B5:B200 on every sheet, then try:

=IF(MAX(INDIRECT("Sheet"&ROW(B2)&"!B$5:B$200"))(T ODAY()-21),"not
due","due")

Greg



"JG" wrote:

I need to copy the formula "=IF(MAX(Sheet2!B5:B200)(TODAY()-21),"not
due","due") in sheet1 from cell b2 to all the cells between b3 and b200. Each
cell should refer to a new worksheet, ie b3 refers Sheet3, b4 to Sheet4 and
so on. When I try to fill it in or copy and paste, only the cell range
changes, and I don't seem to be able to relate it to the sheets required.

I'd be grateful for any help please