Originally Posted by
Sue Vêtements
If you want to get clever, then set up a range to replace the B$2:B$4 part.
If you want to get really clever, you can use the xldown function to define the range, but I forget the syntax for that one.
To define a dynamic range for column B that will expand or contract depending on the data, define a Name (e.g. week1) that refers to:
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
This defines a range that starts at cell B2, with a height as the number of populated cells in column B (assuming no gaps and minus 1 to allow for the column heading) and a width of 1.
Then use this named range in your formula:
=IF(B2 = MAX(WEEK1),"1","0")
xlDown is a VBA constant that is used to specify the direction for the END property of a range, e.g.
Range("$B$B").End(xldown).Row
This could be used (as part of a user-defined function) instead of COUNTA in the above formula.
Vindaloo