Copy Formula Query

[John]

How can I copy a formula down to the next cell (vertically) when I want the
formula within it to reference to a cell that is horizontal?

Example:

There is a formula in G8 of =D3 (in another worksheet). I want to copy the
formula in G8 to G9, but instead of receiving D4 I want it to show E4

It seems such an easy task except I've a lot of these to do and can't figure
out how to do it

Thanks

 

[Frank Kabel]

Hi John
try the following formula in G8 (if you want to change D3 -> E4 in cell
G9 and not to E3):

=OFFSET($D$3,ROW()-8,ROW()-8)
and copy down

if you want to change D3 to E3 in cell G9 use the following in G8
=OFFSET($D$3,0,ROW()-8)

 

[Arvi Laanemets]

Hi

G8=OFFSET(SheetName!$D$3,0,ROW($G8)-ROW($G$8))
or
G8=OFFSET(SheetName!$D$3,ROW($G8)-ROW($G$8),ROW($G8)-ROW($G$8))

The first one, when copied down, refers to D3, E3, F3, ...
The second one, when copied down, refers to D3, E4, F5, ...

 

[John]

Thanks Guys

Hi

G8=OFFSET(SheetName!$D$3,0,ROW($G8)-ROW($G$8))
or
G8=OFFSET(SheetName!$D$3,ROW($G8)-ROW($G$8),ROW($G8)-ROW($G$8))

The first one, when copied down, refers to D3, E3, F3, ...
The second one, when copied down, refers to D3, E4, F5, ...