Array Formula in Merged Cells

C

Charles

Hello

Small problem but it's really bothering me. When you enter an array
formula in a cell, and then merge it with another cell, it becomes
impossible to change the formula. It looks like the only way is to
de-merge the cells and redo it. Anyone knows a way arround this
problem? Do you think microsoft has already received something about
that in their suggestion box?

Thanks in advance
Best regards
Charles
 
D

Don Guillett

Since I always avoid using merged cells, especially in formulas, I had to
test to verify that what you said is true. As they used to say on "That is
the week that was", "VERY interesting"
 
C

Charles

I would actually prefer not using merged cells but that's a very
flexible way to combine Excel and PPT presentation-friendly tables.
When I have to combine several tables with fields of different width on
the same spreadsheet, I just set the columns width to a small size, and
then set the actual size of the fields by merging cells. It also works
without merging when the field contains some text (the text simply
expand on the next cell) but not with formated numbers which display an
ugly "###"

Charles
 
D

Don Guillett

Use center across selection instead
I have a macro in my personal.xls assigned to a custom button on my menu
toolbar.
Sub CENTERACROSS()'Toggle
With Selection
If .HorizontalAlignment = xlCenterAcrossSelection Then
.HorizontalAlignment = xlGeneral
Else
Selection.HorizontalAlignment = xlCenterAcrossSelection
End If
End With
End Sub
Sub CenterAcrossOld()'makes centered
Selection.HorizontalAlignment = xlCenterAcrossSelection
End Sub
 
C

Charles

I tried your script but unfortunately I still get a ### on a formated
number if the column is now wide enough, say a number formated with the
custom format "#,##0;-#,##0;-"

Charles
 
D

Don Guillett

It is designed for you to first select the cells that you want to center
across. My guess is that you selected ONE cell. If a column is not wide
enough you may want to consider making it wider...
 
C

Charles

But that's my whole point. Let's say you want to create a sheet which
contains 2 tables, one above the other (because that's the way it must
be printed or displayed in a PPT). In the first you have 6 columns and
the second you have 2 columns. At one point or another you need to deal
with values displayed over several cells.

Charles
 
Top