Merged cells issue

[froment]

Hi. I have received a file which shows strange behaviour of merged cells: the value displayed in merged cells counts twice towards the sum. Please see attached file.

As far as I know, merged cells work by assigning the value to the top left cell; all other cell in merged range have value zero.

In my example, cells C10 and C11 are merged, and value in that merged cell is 1,900.

However, if you go to another cell and type formula =C10+C11, you get 3,800!

If you type =C10, you get 1,900, and if you type =C11, you get again 1,900! To my understanding, C11 should be zero, shouldn't it?!

If I unmerge cells, I see these duplicate values in each cell. If I merge them again, they behave as exxpected - I get message that only first cell value would be preserved.

I'm opening it with Excel 2016, and I'm pretty sure it was created with 2016, too.

Can someone check what is wrong with this file, please. Thanks in advance.

[Special-K]

Unmerge the cells

You have 1900 in BOTH C10 AND C11.
Hence the 3800.

Put 0 in C11.
remerge the cells and the result is now 1900.

Its the data you've input before merging.


Also, the giveaway is your first sentence.
"I have received a file"

suggesting someone else is respionsible for setting this up.
They've obviously done it wrong.

[FlameRetired]

@ Special-K

Do you have any ideas how it was possible to create such merged cells in the first place?

[tim201110]

https://www.excelforum.com/the-water...-in-excel.html
1st trick

[Glenn Kennedy]

Tim, I'd forgotten about your thread. Finally this reminded me - and I see it now. You said "VBA property Usedrange, is not accurate." what do you mean by that? It strikes me that this has the potential to be very useful for serial mergers... but only if it is accurate.

[FlameRetired]

@ tim201110

Thanks for sharing. That's quite a find and something to stay alert for.

Wow! Merged cells are the devil's spawn after all.

[Glenn Kennedy]

Didn't I tell you...

[tim201110]

Tim, I'd forgotten about your thread. Finally this reminded me - and I see it now. You said "VBA property Usedrange, is not accurate." what do you mean by that? It strikes me that this has the potential to be very useful for serial mergers... but only if it is accurate.

Glenn, here it is.
in the attached file there is a comparison of different methods for usedrange count.
PS It has nothing with this ussue , It is about autofill

[froment]

Thanks a lot for support, guys, and especially to Tim for solution.

Weird, I did not know this, nice to learn it; but when I think of it, it makes sense: when you paste only formats, you tell Excel not to touch anything but the formats, so values of all cells remain the same, hence the value in all merged cells...

Unmerge the cells

You have 1900 in BOTH C10 AND C11.
Hence the 3800.

Put 0 in C11.
remerge the cells and the result is now 1900.

While I appreciate your prompt attempt to help, I have to say it was off the mark - of course, I knew I could unmerge it and merge manually, but I wondered how did the values appear in all merged cells instead in the top left cell of merged region, as it happens when you merge the cells.