Fai la tua domanda

Allister's profile - attività

2017-02-01 00:12:09 +0200 received badge  Entusiasta
2016-11-11 02:47:37 +0200 ha risposto a una domanda Cells no longer match text after copy paste

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 (?)

2016-11-11 02:26:35 +0200 received badge  Revisore (source)
2016-11-11 02:25:13 +0200 ha posto una domanda Cells no longer match text after copy paste


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):


In F13 I have a formula:


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:


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):


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 Update to try to fix to (x64)

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

Any thoughts on this? Thanks!