Access to the value of a merged cell in a formula

[Zlika]

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

 

[Guest]

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)

 

[Guest]

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.

 

[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).

 

[Ron Rosenfeld]

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

 

[Guest]

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.

 

[Zlika]

Thank you very much for your help

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

 

[Myrna Larson]

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.

 

[TryTek]

Simpler answer using a function named ReadMarket.
Where you have a Column which has multiple Merged row groups with varying size.
If you want the value in A3 use ReadMarket(A3), A15 use ReadMarket(A15), etc.
Here is the function placed in Module 1.

Public Function ReadMarket(ByVal Target As Range)
ReadMarket = Target.MergeArea.Cells(1, 1)
End Function

You may name it whatever you like, shorter is better.
Good Luck