Referencing a merged cell as a whole as required?

As far as my very badly worked research went, I found out you have to reference the top left-most underlying cell in the cluster of merged cells to get what the cluster contains; trying to reference any other underlying cell returns nothing.

That’s unacceptable, it either additionally complicates formulas or forces to do extra manual work.

How would such merged cell be correctly referenced then ? Attached the test file: Android, Forge of Empires.ods

In the file go to ‘Supplies buildings’ tab.

In columns D and E there are 5-row clusters of merged cells and each merged cell contains a number.

Column F is where each cell needs the clusters of merged cells to perform a calculation, but due to the requirement only the 1st row for each cluster performs the calculation successfully.

(Not sure about the tags right now)

There isn’t actually something like a “merged cell”. Merging is just a question of formatting. The area of one (the topmost leftmost cell gets expanded to cover the areas of all the other cells of the “merged” range.

If some of the cells to “vanish” have content you will be prompted to make a choice where ‘Keep the contents of the hidden cells’ should be the preselected default.

If you accept this, the hidden cells will keep their content and can be referenced to pass it (or the result in case of a formula) to any formula independently.

If you choose ‘Empty…’ the contents of the hidden cells will be deleted. The cells themselves will still exist and answer as any blank cell if referenced without precautions (number 0). The option placed first will rarely make sense, I suppose. (It concatenates inserting spaces.)

The “cluster” doesn’t contain anything. It simply doesn’t exist.

A rule to consider: Merging is bad and tends to makes things topsy-turvy. The only exception I know are a few headers concerning more than one columns.

Generally try to make sure that each row a content is assumed for actually possesses this content in the respective cells.

In your specific case the background cells in columns D and E are all blank. The SUM function can therefore provide a provisional solution. (SUM($E$2:$E$7) instead of $E2 and respectively in F2:F7 …) Maintenance and scaling of sheets containing a lot of merging can easily become a nightmare anyway. The next better, but still iunsatisfying solution is, to release merging for a session and to enter formulae like =E2 into E3 and respectively into all the concerned cells. Then you may reinstate the merging.

See also this workaround.

What would be more senseful than ‘merged cells’, or a ‘cluster of merged cell’ where ‘cluster’ is the single large cell consisting of the multiple cells merged ? AFAICT, nothing else.