How do you force a copy to increment by 1 in Excel?

[Guest]

I have 5 rows I need to copy as a group (up to 500 times). Columns A, C and
D are merged cells and when I copy (or autofill) the 5 rows as a group the
references in those cells increment by 5. I need them to only increment by 1.

 

[broro183]

Hi,
First up, you say "Columns A, C and D", do you mean "Columns A, *B,*
and D"?
I would remove the merged cells b/c they can cause a range of problem
& use the option to "centre across selection" which can be found unde
[format - cells - alignment] & then the "horizontal" dropdown.

Sorry, I can't replicate this issue - my equations go up by 1, can yo
please provide an example of the formulae that are being copied?
(If no one else responds in the mean time I'll check it out after slee
& work.)

A potential solution involves the use of the "offset" function.

hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[Guest]

Sorry if there was any confusion, I have posted this a couple of times and
details get left out.

5 rows - 4 through 8
Columns A, B, C and D contain merged rows (4-8) and a reference to a cell on
another worksheet (there is other data in Columns out to AF).
When I try to copy (or Autofill) Rows 4-8 to 9-13 and so on (up to 500
times) the references in columns A, B, C and D increment by 5. I need them
to only increment by 1

 

[broro183]

Hi,
The problem is caused by the merged rows, anything that is merge
"takes on" the identity of the top left cell in the merged group. Thi
is standard Excel behaviour, effectively you are turning the 5 row
into a single row. For example, try filling cells A1 to A5 wit
different values & then pressing the merge & centre icon, this result
in a warning "the selection contains multiple data values. Merging int
one cell will keep the upper-left most data only.".

AFAIK the only way to correct this is to unmerge the rows/cells, red
the formulae & then the cells will increment by one.

btw, if the cells have been merged for appearance this can probably b
achieved with unmerged cells by adjusting the height of the rows/th
font size etc.

hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience..