Fill array with fn variables that vary across and down?
 

I have an RxC array which contains formulas in each cell. The formula has a
variable that varies vertically & horizontally, and a variable that varies
horizontally by row: E.g:
=E2-$O$2, =F2-$O$2, ...
=E3-$O$3, =F3-$O$3, ...
....
I would like to find a better way to populate the array as I add rows. I
would greatly appreciate a shortcut or macro or ?? that will help me do
this. The first variable works properly when I fill right and down, but the
second one ($O$2) varies by row, but then stays the same across the width of
the array. Thanks much!

dmn

"Llurker" wrote in message
. ..
I have an RxC array which contains formulas in each cell. The formula has a
variable that varies vertically & horizontally, and a variable that varies
horizontally by row: E.g:
=E2-$O$2, =F2-$O$2, ...
=E3-$O$3, =F3-$O$3, ...
...
I would like to find a better way to populate the array as I add rows. I
would greatly appreciate a shortcut or macro or ?? that will help me do
this. The first variable works properly when I fill right and down, but
the second one ($O$2) varies by row, but then stays the same across the
width of the array. Thanks much!

If I understand correctly =E2-$O2 should help.

Ian

Hi, Ian--

Thanks, but that's not quite the right thing. Sorry if I mis-explained.
$O2 will vary the 2 across and down as I fill the array, or add a row.
What I'm looking for is something that will vary it down, but keep it
constant in each row:
row 1 (4 cols): $O$2, $O$2, $O$2, $O$2
row 2 (4 cols): $O$3, $O$3, $O$3, $O$3
row 3 (4 cols): $O$4, $O$4, $O$4, $O$4
etc.
I tried doing something like =D2-$ORow(), which I hoped would resolve to
D2-$O3 (for example).
Is there a way to create a cell reference like this?

Thank you for your help!

dmn

"IC" wrote in message
...

"Llurker" wrote in message
. ..
I have an RxC array which contains formulas in each cell. The formula has
a variable that varies vertically & horizontally, and a variable that
varies horizontally by row: E.g:
=E2-$O$2, =F2-$O$2, ...
=E3-$O$3, =F3-$O$3, ...
...
I would like to find a better way to populate the array as I add rows. I
would greatly appreciate a shortcut or macro or ?? that will help me do
this. The first variable works properly when I fill right and down, but
the second one ($O$2) varies by row, but then stays the same across the
width of the array. Thanks much!

If I understand correctly =E2-$O2 should help.

Ian

I figured out that I can use the offset function to do what I need. That
allows me to vary the row and keep the column offset fixed for as wide an
array as I need.

=E2-offset($O2,0,5) this formula is the same in all fields of the array; it
varies by row but not by column.

"Llurker" wrote in message
. ..
I have an RxC array which contains formulas in each cell. The formula has a
variable that varies vertically & horizontally, and a variable that varies
horizontally by row: E.g:
=E2-$O$2, =F2-$O$2, ...
=E3-$O$3, =F3-$O$3, ...
...
I would like to find a better way to populate the array as I add rows. I
would greatly appreciate a shortcut or macro or ?? that will help me do
this. The first variable works properly when I fill right and down, but
the second one ($O$2) varies by row, but then stays the same across the
width of the array. Thanks much!

dmn