software

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.


Hi,
First up, you say quot;Columns A, C and Dquot;, do you mean quot;Columns A, *B,* C
and Dquot;?
I would remove the merged cells b/c they can cause a range of problems
amp; use the option to quot;centre across selectionquot; which can be found under
[format - cells - alignment] amp; then the quot;horizontalquot; 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
amp; work.)

A potential solution involves the use of the quot;offsetquot; function.

hth,
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=506730Sorry 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

quot;broro183quot; wrote:

gt;
gt; Hi,
gt; First up, you say quot;Columns A, C and Dquot;, do you mean quot;Columns A, *B,* C
gt; and Dquot;?
gt; I would remove the merged cells b/c they can cause a range of problems
gt; amp; use the option to quot;centre across selectionquot; which can be found under
gt; [format - cells - alignment] amp; then the quot;horizontalquot; dropdown.
gt;
gt; Sorry, I can't replicate this issue - my equations go up by 1, can you
gt; please provide an example of the formulae that are being copied?
gt; (If no one else responds in the mean time I'll check it out after sleep
gt; amp; work.)
gt;
gt; A potential solution involves the use of the quot;offsetquot; function.
gt;
gt; hth,
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...
gt;
gt;
gt; --
gt; broro183
gt; ------------------------------------------------------------------------
gt; broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
gt; View this thread: www.excelforum.com/showthread...hreadid=506730
gt;
gt;


Hi,
The problem is caused by the merged rows, anything that is merged
quot;takes onquot; 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 amp; then pressing the merge amp; centre icon, this results
in a warning quot;the selection contains multiple data values. Merging into
one cell will keep the upper-left most data only.quot;.

AFAIK the only way to correct this is to unmerge the rows/cells, redo
the formulae amp; 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 amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=506730