Zlika -

Since we can't exploit your datastructure, I don't believe there is a
built-in formula (or any combination thereof) that will accomplish your goal.
So a user defined function is the next logical step and Ron has provided a
definitive one.

---
Jay

"Zlika" wrote:

Thank you for your help.
However, it does not fit my needs, because I can have an unknown number of
cells merged.

For example, I can have A1 and A2 merged, but I can also have A1,A2 and A3
etc...

I would want a generic approach which would give me the value of the merged
cells (i.e. the value of A1 in fact) whatever the cell I consider (for
example, if A1=12 and I merge A1,A2 and A3, I would want Excel to display
"12" when I type "=A1","=A2" or "=A3", because I do not know if A2 and/or A3
have been merged with A1).

"Jay" a écrit dans le message de news:
...
Hi Zlika -

There is an error in my first response.

Change "c1" to "b2" in the description of Approach 2 as follows:

Approach 2. Step a - In cell b1, enter this formula: =IF(A1="","",A1)
Step b - Then, in cell c1<<<change this 'c1' to 'b2', enter this
formula
and copy it downward:
=IF(A2="",B1,A2).

Sorry for any inconvenience this may error may have caused.
---
Jay


"Jay" wrote:

Hi Zlika-

I don't believe there is a standard way of dealing with your situation as
I
understand it. However, we may be able to exploit your data structure if
it
is consistent.

I'm not certain from your description whether you want blanks in your
formula column when A2 is empty. Using your example of merged cells in
column
A, you could try one of these two approaches:

Approach 1: In cell b1, enter this formula and copy it downward:
=IF(A1="","",A1)

Approach 2. Step a - In cell b1, enter this formula: =IF(A1="","",A1)
Step b - Then, in cell c1, enter this formula and copy it downward:
=IF(A2="",B1,A2)

---
Jay


"Zlika" wrote:

Hi everybody,
In my worksheet, the A1 cell is merged with the A2 cell (for example).
My
problem is that when I want to access to the value of the A2 cell (by
typing
"=A2" in another cell), it returns 0 instead of the value of the merged
cell
(which is in fact the value of the A1 cell).

I know how to do in VBA (using .MergedArea.Cells(1,1) to access to the
value
of the first cell of the merged area) but not directly in an Excel
formula
(and I do not know in a general case that A1 and A2 are merged, so I
cannot
use "=A1").

Do someone know how to do it?
Thank you very much

Zlika