I have a function that refers to a cell on a different worksheet, say A1, and
in the adjacent cell I have another function that refers to another cell on
the other worksheet, but in a different block, say AA1, for example. I want
to select both cells and drag them across to reference B1 and AB1, C1 and
AC1, etc in adjacent cells, but when I do it yields C1 and AC1, E1 and AE1,
etc, effectively skipping every other column in each block. I can see why its
doing it, and I can get around it with some cutting, pasting and faffing, but
I was wondering if anyone knew of a work around???

Put the second formula the next line down, then copy across. This will
eliminate the skipping.

A1 B1 C1...
AA1 AB1 AC1...

Another way is to go ahead and put the correct reference in the next two
cells, then copy all four cells across the page. You are in effect creating
a pattern...

A1 AA1 B1 AB1 (copy all four of these, instead of just the first two)

"rmellison" wrote:

I have a function that refers to a cell on a different worksheet, say A1, and
in the adjacent cell I have another function that refers to another cell on
the other worksheet, but in a different block, say AA1, for example. I want
to select both cells and drag them across to reference B1 and AB1, C1 and
AC1, etc in adjacent cells, but when I do it yields C1 and AC1, E1 and AE1,
etc, effectively skipping every other column in each block. I can see why its
doing it, and I can get around it with some cutting, pasting and faffing, but
I was wondering if anyone knew of a work around???

Thanks, but I can't really use the first method. My two adjacent functions
are in fact one of about 500 rows in the column, so I can't relly have one
below the other without then selecting every other one and cutting and
pasting to a new column.

I have in fact worked around the problem now (albeit in a very round about
way using INDIRECT,ADDRESS, ROW COLUMN etc with a helper row of 0,1,2,3... in
every other cell). I also tried the second method out of curisoity (and to
save time in the future!), but the references were still incorrect. When I
drag 4 correctly refenced cells it still reverts back to refencing every
other cell, ie still refencing according to the column where the formula is.

As I said, not critical, but if you have any further suggestions I'm all
ears...

"JR" wrote:

Put the second formula the next line down, then copy across. This will
eliminate the skipping.

A1 B1 C1...
AA1 AB1 AC1...

Another way is to go ahead and put the correct reference in the next two
cells, then copy all four cells across the page. You are in effect creating
a pattern...

A1 AA1 B1 AB1 (copy all four of these, instead of just the first two)

"rmellison" wrote:

I have a function that refers to a cell on a different worksheet, say A1, and
in the adjacent cell I have another function that refers to another cell on
the other worksheet, but in a different block, say AA1, for example. I want
to select both cells and drag them across to reference B1 and AB1, C1 and
AC1, etc in adjacent cells, but when I do it yields C1 and AC1, E1 and AE1,
etc, effectively skipping every other column in each block. I can see why its
doing it, and I can get around it with some cutting, pasting and faffing, but
I was wondering if anyone knew of a work around???