To merge cells

[Guest]

Hi,

Is it possible to merge 2 cells and to keep the value of the cells in each
one of them ?
Thus, cell 1 always contains its value and cell 2 still contains its value
but they are merged.

Thank you.

 

[David McRitchie]

Hi Patrick,
The answer is No, just as you surmised, because you will only see the
one value if you unmerge the merged cells.
..
The cell that gets or retains the value is the cell in the upper left corner of the merged cells.

Merge and Unmerge Cells
http://www.mvps.org/dmcritchie/excel/merge.htm

 

[Mark]

Maybe I'm being dull but if they still contain their original values then
surely they are not merged?

Sorry if I'm barking up the wrong tree but are you just looking to put a
single border arround 2 cells so they appear as one but remain seperate in
terms of holding data?

Mark

 

[Guest]

Thank you for your answer. And I agree with with you. But, at work, we have a
workbook (created before I was hired in the company) how contains cells as I
spoke. It's really strange because I can't do it again.

If you would like to it, I can send it to you.

Thank you.

 

[David McRitchie]

Hi Patrick,
Maybe it did something else like placing a shape on top of the
cell, a text box on top of cell, or white-out the font in the right cell.

You can send me the file if you don't see it. and you are allowed to, but first
try selecting each of the two cells
do you see a value in the cell and/or on the formula bar
check the formatting of cell(s), is it general
Select ALL cells then Edit, GoTo (Ctrl+G), Special button, objects
which would find all shapes on the page.l

 

[Ken Wright]

Just guessing, but is it by any chance what appears to be a single cell that
has been divided diagonally by a border and yet appears to contain a value
in each half of the diagonal??

If so, then that is done by formatting the cell with a diagonal border,
entering a value, hitting ALT+ENTER to force a new line within the cell,
adding some spaces and then entering the second value. All the data is one
cell but looks like it is split between the border

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[David McRitchie]

Hi Patrick,
Had to see the spreadsheet myself to believe it, but once seen, was able to
reproduce in Excel 2000 as follows:

Fill several cells up then
1) Merge any two cells vertical or horizontal, inside or outside used range - doesn't matter
2) use the "Format Painter" tool bar icon to copy the merged cells format, to other cells
which then show up as merged.

When the secondary merged cells are unmerged they still have their
original values.

 

[Ken Wright]

What on earth!!!

Excel 2003 as well

Who'd have thunk it!! Another reason not to use the damn things - so damned
unpredictable

Regards
Ken..................

Hi Patrick,
Had to see the spreadsheet myself to believe it, but once seen, was able
to
reproduce in Excel 2000 as follows:

Fill several cells up then
1) Merge any two cells vertical or horizontal, inside or outside used
range - doesn't matter
2) use the "Format Painter" tool bar icon to copy the merged cells
format, to other cells
which then show up as merged.

When the secondary merged cells are unmerged they still have their
original values.

 

[RagDyer]

AND 97 too.

And even 97 SR-1 also.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

What on earth!!!

Excel 2003 as well

Who'd have thunk it!! Another reason not to use the damn things - so damned
unpredictable

Regards
Ken..................

 

[Dave Peterson]

Nice detective work, David.

And this could be dangerous with formulas like:
=sum(a:a)
=sum(a:b)
=sum(b:b)
If you think those hidden (where are they?!) values are gone.

You may want to share it with John Walkenbach for his excel oddities page:
http://j-walk.com/ss/excel/odd/index.htm

And while I was playing with it, I put some formulas in those cells that were
soon to become hidden behind the merged cells.

I merged A1:K1
then used the format painter to paint over A2:K2, A3:k3, ...A10:K10.

Unmerging A2:K2,...A10:K10 brought back the formulas, too.

I had a formula in D2.

I put this in a cell (D28):
=D2

D28 showed the results of the formula in D2 no matter if that cell was "hidden"
or not.

It might be a way of hiding those proprietary formulas <bg>.

Formatting (font/fill/conditional formatting) didn't make it out alive, though.

Data|Validation on one of those hidden cells did make the round trip--from excel
to the twilight zone and back.

Hi Patrick,
Had to see the spreadsheet myself to believe it, but once seen, was able to
reproduce in Excel 2000 as follows:

Fill several cells up then
1) Merge any two cells vertical or horizontal, inside or outside used range - doesn't matter
2) use the "Format Painter" tool bar icon to copy the merged cells format, to other cells
which then show up as merged.

When the secondary merged cells are unmerged they still have their
original values.

 

[Dave Peterson]

Ps.

I used this when I was screwing around and saw the values in both columns A&B of
rows 2-8 (after I merged with the format painter.

Option Explicit
Sub testme()

Dim iRow As Long
Dim iCol As Long

For iRow = 1 To 8
For iCol = 1 To 2
MsgBox Cells(iRow, iCol).Address(0, 0) _
& "--" & Cells(iRow, iCol).Value & _
vbLf & Cells(iRow, iCol).MergeArea.Address(0, 0)
Next iCol
Next iRow

End Sub

Hi Patrick,
Had to see the spreadsheet myself to believe it, but once seen, was able to
reproduce in Excel 2000 as follows:

Fill several cells up then
1) Merge any two cells vertical or horizontal, inside or outside used range - doesn't matter
2) use the "Format Painter" tool bar icon to copy the merged cells format, to other cells
which then show up as merged.

When the secondary merged cells are unmerged they still have their
original values.

 

[David McRitchie]

I already suggested in email to Patrick that he share it on "Excel oddities"
After all he was the one who originally did it, discovered that it
wasn't expected behavior, probably would not continue to be so,
and maintained a copy of the worksheet.

Actually he emailed to to me 2 days ago and it was stuck in spam
filters, so a little luck was involved as well.