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

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

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

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

On Fri, 2 Nov 2007 09:51:38 +0100, "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


I think you will have to use a UDF in your formulas in place of any cell
reference which might be part of a merged area.

The UDF can be pretty simple. e.g.


=======================
Function mr(rg As Range)
If rg.Count = 1 Then
mr = rg.MergeArea(1, 1)
Else
mr = CVErr(xlErrRef)
End If
End Function
=======================

The code above will give a REF error if rg is not a single cell.
--ron

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

Thank you very much for your help

"Ron Rosenfeld" a écrit dans le message de news:
...
On Fri, 2 Nov 2007 09:51:38 +0100, "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


I think you will have to use a UDF in your formulas in place of any cell
reference which might be part of a merged area.

The UDF can be pretty simple. e.g.


=======================
Function mr(rg As Range)
If rg.Count = 1 Then
mr = rg.MergeArea(1, 1)
Else
mr = CVErr(xlErrRef)
End If
End Function
=======================

The code above will give a REF error if rg is not a single cell.
--ron

Merged cells, are you are demonstrating, cause more problems than they solve!

What you really ought to be thinking about is getting rid of those merged
cells. If they are only for the purpose of formatting, look at the Center
Across Selection option under Format/Alignment/Horizontal.

On Fri, 2 Nov 2007 18:31:17 +0100, "Zlika" wrote:

Thank you very much for your help

"Ron Rosenfeld" a écrit dans le message de news:
...
On Fri, 2 Nov 2007 09:51:38 +0100, "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


I think you will have to use a UDF in your formulas in place of any cell
reference which might be part of a merged area.

The UDF can be pretty simple. e.g.


=======================
Function mr(rg As Range)
If rg.Count = 1 Then
mr = rg.MergeArea(1, 1)
Else
mr = CVErr(xlErrRef)
End If
End Function
=======================

The code above will give a REF error if rg is not a single cell.
--ron