Copy formula and not skip rows

[khoffmansrhc]

I am trying to copy a group of two formulas down a column for an example lets say they are the following:

=A1
=B1

When I copy those two formulas together down a column, of course I get the following:

=A1
=B1
=A3
=B3
=A5
=B5

In my above example the even numbered rows are skipped. Is there a trick to get the following result when I paste the two formulas together?

=A1
=B1
=A2
=B2
=A3
=B3

I need to paste the two formulas down the column approximately 4,000 times so going back and editing each individual cell is not a good solution. Thank you in advance for your help.

 

[PWD]

Hi. Not a solution but possibly an explanation as to why it's not working - although this may lead to clues as to what to do; I don't know. I put

=A1
=B1
=A2
=B2

into cells "D14" and wondered what each actually meant so I changed the formula display to RC and this is the result:-

D1=RC[-3]
D2=R[-1]C[-2]
D3=R[-1]C[-3]
D4=R[-2]C[-2]

So it appears that there's no logical sequence (that I can see), which is why the copying down won't work.

Sorry if this isn't much help, but who knows??

Many thanks,
D€$

 

[SkipVought]

hi,

You may want water to be dry, but desire cannot make it so. This is the way that formulas work when copied and pasted.

You may want to use a lookup formula, rather than a direct reference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

In line with Skip's suggestion (sort of), you can use the row of the cell you are in to help define which row you want to take your reference from. There are many ways to do this, for example using OFFSET.

For example, if in cells A1:A20, you want to refer to cells B1:B10 on the odd rows and C1:C10 on the even rows, i.e.
cell A1 refers to B1
cell A2 refers to C1
cell A3 refers to B2
cell A4 refers to C2
etc

The you could use the following formula:

in cell A1, put =OFFSET($B$1,(ROW(A1)-1)/2,MOD(ROW(A1)+1,2),1,1)

Then copy cell A1 down to cells A1:A20. When you do so, cell A2 will then contain:
=OFFSET($B$1,(ROW(A2)-1)/2,MOD(ROW(A2)+1,2),1,1)
and so on...

If you don't understand the above, look up the help on:
absolute Vs relative references, and the OFFSET, ROW, and MOD functions.

You could also do it using INDEX instead of OFFSET (with a different formula).

There are, in fact, a lot of ways of achieving this sort of referencing (for example, see INDIRECT and ADDRESS), but this should get you going for now.

Tony