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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
>
> "Llurker" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
news:[email protected]...
>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
>
>