I'm using a formula that checks if a day falls between 2 dates that is listed in another sheet.
These dates are listed in 2 columns: B and C
The dates run down 10 rows each time, after which a 5 row break is in place before the next sequence begins
The problem is that I can't drag my formula down the normal way, because it just adds +1 row.
I've read about using "OFFSET", but I can't figure out how to add this to my formula.
In my case the next row should add "+14" to all values in B and C column.
The formula I'm using is:
Formula:
=IF(OR(C$3=MEDIAN(C$3,Dates!$B6:$C6),C$3=MEDIAN(C$3,Dates!$B7:$C7),C$3=MEDIAN(C$3,Dates!$B8:$C8),C$3=MEDIAN(C$3,Dates!$B9:$C9),C$3=MEDIAN(C$3,Dates!$B10:$C10),C$3=MEDIAN(C$3,Dates!$B11:$C11),C$3=MEDIAN(C$3,Dates!$B12:$C12),C$3=MEDIAN(C$3,Dates!$B13:$C13),C$3=MEDIAN(C$3,Dates!$B14:$C14),C$3=MEDIAN(C$3,Dates!$B15:$C15)),1,0)
The formula on the following row should be:
Formula:
=IF(OR(C$3=MEDIAN(C$3,Dates!$B20:$C20),C$3=MEDIAN(C$3,Dates!$B21:$C21),C$3=MEDIAN(C$3,Dates!$B22:$C22),C$3=MEDIAN(C$3,Dates!$B23:$C23),C$3=MEDIAN(C$3,Dates!$B24:$C24),C$3=MEDIAN(C$3,Dates!$B25:$C25),C$3=MEDIAN(C$3,Dates!$B26:$C26),C$3=MEDIAN(C$3,Dates!$B27:$C27),C$3=MEDIAN(C$3,Dates!$B28:$C28),C$3=MEDIAN(C$3,Dates!$B29:$C29)),1,0)
I've attached an example with what I would like to accomplish. I just don't want to manually change all the formulas all the way down 135 rows.
Any help is much appreciated.
Bookmarks