# Globally setting X number of rows earlier

#### RJAY

##### New Member
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 ?

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### RJAY

##### New Member
For some reason the formula got corrupted when I posted it, and I have to post it as PHP for it to work :
PHP:
``=IF(AND(H16<V11,I16>W11),1/1,"0")``
<v11,i16><v11,><v11,><v11,i16><v11, i16=""><v11,i16></v11,i16></v11,></v11,i16></v11,></v11,></v11,i16>

Last edited:

#### Fazza

##### MrExcel MVP
Hi,

Maybe like this. Please see Excel help for details of the (volatile) OFFSET function. OFFSET is (from a cell, so many rows, so many columns)

Say the formula is in cell A17 and the number of rows to offset is in X1. I have assumed positive value 6, 7 or 8 is offset that many rows upwards - though normal convention is that an offset in that direction is negative.

(The code tags have corrupted the formula. Trying again.)

PHP:
``=IF(AND(H16OFFSET(A17,-X1,22),1/1,"0")``

And if the formula is in a different column, say 10 columns in from A17 in cell K17 the formula is a little different in that the columnar offset is now 10 rows less. Viz,
PHP:
``=IF(AND(H16OFFSET(K17,-X1,12),1/1,"0")``

Again, the first formula is for cell A17 and the second for cell K17 (offset 10 columns in from cell A17).

HTH, Fazza

Last edited:

Replies
4
Views
92
Replies
1
Views
71
Replies
3
Views
120
Replies
6
Views
134
Replies
12
Views
346

1,191,559
Messages
5,987,288
Members
440,087
Latest member
Ruppert23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back