Ask Your Question
0

Cells no longer match text after copy paste

asked 2016-11-11 02:25:13 +0200

Allister gravatar image

updated 2016-11-11 02:27:49 +0200

Hi,

TL;DR; Copying cells down one cell and updating references no longer matches text as it did in first row of cells.

I have a set of cells that I wanted to cut/paste (or copy/paste/delete first row) so that I could still use a sumif statement to read across the top and and information relevant from those columns as it had before.

Starting in P11 and going to AL11 I have a number of columns (Using Bold), thus

Placement |Site | Rating | Placement |Site | Rating | Placement |Site | Rating

In F11 I have (In a spanning cell, Using bold):

Placement

In F13 I have a formula:

=SUMIF($P$11:$AL$11,$F$11,$P13:$AL13)

This All Works - All columns information in the row that have a header of "Placement" are added together.

I then Cut/Paste P11:AL11 to P12:AL12. Then I re-visit F13 and change the formula to:

=SUMIF($P$12:$AL$12,$F$11,$P13:$AL13)

Then fill-down (via drag or CTL-D)The first row sums to 1 (first number it sees under a placement). The second row, previously, summed to 16 - but is now #N/A (as are all following rows).

Here is a copy/paste of row 18 (that now reads #N/A):

=SUMIF($P$12:$AL$12,$F$11,$P18:$AL18)

Now if I copy/paste P12:AL12 back into P11:AL11 all the numbers sum as they should under each heading of Placement.

If I delete P12:AL12, now F13 breaks and F14 sums only the first number it sees under a "Placement". All other cells turn to 0.

I thought maybe its something to do with range/column naming and have checked under Insert > Named Expressions > [Everything]. But there are no names or defined column headings.

Version was 5.2.2.2 Update to try to fix to 5.2.3.3 (x64)

I have tried multiple "Paste Special" options, including all. As well as copying/pasting each cell down individually.

Any thoughts on this? Thanks!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-11-11 02:47:37 +0200

Allister gravatar image

Reopen sheet where P11:AL11 is now P12:AL12.

Items with formula from previous attempt that follow the 12 range are normal. Newly updated/filled cells don't update when I do a Fill Up.

Then just added a row above since nothing above was being worked on. Gah.

/Finds handy wall [bonk]

Unless I'm goofy, this still might be something to look into (?)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-11 02:25:13 +0200

Seen: 67 times

Last updated: Nov 11 '16