Poll

Aboot this blog

This blog is a cross between a scrapbook and a diary. I hope you find something here of interest. If you’d like to keep up with things as I see them then you can subscribe to my news feed by clicking the icon below:

feed-icon-28x28.png

DISCLAIMER

Please note that any reviews/recommendations are based solely on my own experience and does not constitute a guarantee that you will have the same experience. Please do your own research before parting with any money - the risk is all yours!

The Briticiser

Looking for my world famous US to GB English spelling converter?

THE “BRITICIZSER”

www.us2uk.eu

IT’S HERE

Tags

  Bits and Bobs     Acrobat     Software     Equipment     Education     News     3D Stuff     Utilities     Family     Reviews     Windows     Bookmarks         Hardware     Browsers     Word     Overcoming Disabilities     Flatpress     Excel     PHP     Plausible Lies     Tutorials     Odds and Sods     VBA  

Find me on Facebook

facebook-icon-28x28.pngVisit my Briticiser Facebook Page to keep up with things here - lots of reviews of stuff I’ve really used and also all kinds of helpful computer tips with a heavy bias towards SAVING YOU TIME.

Merging cells in Excel can lead to formula errors

Permalink: http://paul.us2uk.eu/?x=entry:entry160930-180600

As I’ve just discovered after so many years of using Excel if you merge a group of cells and then use a lookup (I only ever use VLOOKUP but it will probably apply to HLOOKUP too) to return the value from that merged group of cells, then you run the risk of getting the wrong result.

Here’s why, and why you should NEVER use merged cells in part of your spreadsheet which you might (one day) want to look up date from via a formula….

Take a look at the following formula - there’s nothing complex about it but it returns the wrong answer.

Returning a look up value from a group of merged cells sometimes gives you the wrong answer

Now, I knew that the result was coming from a merged group of four cells but I thought that the value shown in the merged cell would be the same for all four cells, but this is not necessarily the case…

It can happen - perhaps through innocently copying and inserting rows - that each of those cells has a hidden value, and you can only find that out by de-merging the suspect cells, like so:

De-merging cells can reveal some unexpected surprises

To be fair to myself I’d never intended to use this column for the result of a lookup formula and it’s probably the first time I’ve ever done it, but the problem now remains as to whether the same formula has returned incorrect results in one of the other hundreds of rows where I’ve looked up values in a merged group of cells.

Fortunately there is a macro you can run to find all merged cells in a worksheet, split (de-merge) them, and fill each of the de-merged cells with the value shown on the left-uppermost cell (or just the first value if you have all your cells in once column, as I have above)…

http://stackoverflow.com/questions/9215022/unmerging-excel-rows-and-duplicate-data

…but the lesson here is to NEVER use merged cells in part of your spreadsheet which you might (one day) want to look up date from via a formula.

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.

Support our caregiving

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.