Copy Formula Query

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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)
 
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, ...
 
Thanks Guys


Arvi Laanemets said:
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, ...
Click to expand...
 
Back
Top