I have the current formula that references a value 6 rows back:
=IF(AND(H16<V11,I16>W11),1/1,"0")
and is used in thousands of cells (so the cell numbers increment by one each time).
I would instead like to specify the number of rows back elsewhere in the spreadsheet so that I can easily change it from (say) 6 rows back to 7 or 8.
So that V11 and W11 are replaced by V10 / W10 if I've chosen 7 rows back, V9 / W9 is I've chosen 8 rows back, etc.
I know that I could have a cell somewhere with the value of 6, 7 or 8 in it, but how would I reference that cell in the above formula ?
=IF(AND(H16<V11,I16>W11),1/1,"0")
and is used in thousands of cells (so the cell numbers increment by one each time).
I would instead like to specify the number of rows back elsewhere in the spreadsheet so that I can easily change it from (say) 6 rows back to 7 or 8.
So that V11 and W11 are replaced by V10 / W10 if I've chosen 7 rows back, V9 / W9 is I've chosen 8 rows back, etc.
I know that I could have a cell somewhere with the value of 6, 7 or 8 in it, but how would I reference that cell in the above formula ?