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

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.

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

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

Sorry, I can't replicate this issue - my equations go up by 1, can you
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 sleep
& 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...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=506730

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

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" wrote:


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

Sorry, I can't replicate this issue - my equations go up by 1, can you
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 sleep
& 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...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=506730

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

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

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

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

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=506730