Accessing non-topleft value in a group of merged cells in Excel

N

nitin.bhagwath

Hello all,

I have a group of virtically merged cells. I can't find a way of
accessing one of the inner values of this range of merged cells
(non-topleft values) to use in a formula.

As an example,

A B C
1 1 1 1
2 <2> 1 2
3 <2> 1 0
4 3 1 3

Here, A2 and A3 are merged, and C=A*B (B is all 1 for simplicity). In
this case, C3 will yield 0 because the value returned by A3 (a non-top
merged cell) yields 0. I would like C3 to also be 2, by forcing A3 to
take the value of the merged range it is a part of.

I know I can unmerge the cells, and manually enter in the values in
each of the cells (A2 and A3 in this case). But I would like to retain
the look-and-feel of a merged cell while being able to work with
formulae.

Is there any way of doing this, or is unmerging my only option?
Thanks in advance.
-Nitin
 
K

keepITcool

Nitin,


You can. By exploiting a bug :) When you use format painter you can
merge cells without losing the formulas in the non topleft cells.


First unmerge.
in the topleft cell is the actual formula
in the "remainder" cells make a simple reference to the topleftcell.

now select an empty range with the same dimension as your
intended merge range.

now merge the empty cells.

select the empty merged range and press the format painter button
and paint the actual cells you want to merge.

this (should) merge the cells without the loss of the formulas.

although you cannot select (or point) to the non visible cells
when you build a formula, you can type them.
 
N

nitin.bhagwath

Excellent! This works.

It's a shame that this option isn't available as a regular feature (a
"check button" under format, perhaps?)

Thanks.
 
Top