Ask Your Question
0

Cells no longer match text after copy paste [closed]

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

Allister gravatar image

updated 2020-08-11 17:03:31 +0100

Alex Kemp gravatar image

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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-11 17:03:08.788387

1 Answer

Sort by » oldest newest most voted
0

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

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

Question Tools

1 follower

Stats

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

Seen: 110 times

Last updated: Nov 11 '16