Globally setting X number of rows earlier

RJAY

New Member
Joined
Feb 22, 2008
Messages
11
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
For some reason the formula got corrupted when I posted it, and I have to post it as PHP for it to work :confused: :
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top