To merge cells

P

Patrick Fredin

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.
 
P

Patrick Fredin

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.

--
Patrick


David McRitchie said:
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Patrick Fredin said:
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.
Click to expand...
Click to expand...
 
D

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

Patrick Fredin said:
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.

--
Patrick


David McRitchie said:
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Patrick Fredin said:
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.
Click to expand...
Click to expand...
Click to expand...
 
K

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 :)
------------------------------­------------------------------­----------------


Patrick Fredin said:
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.

--
Patrick


David McRitchie said:
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Patrick Fredin said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Patrick Fredin said:
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.

--
Patrick


David McRitchie said:
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Patrick Fredin said:
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.
Click to expand...
Click to expand...
Click to expand...
 
K

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

David McRitchie said:
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Patrick Fredin said:
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.

--
Patrick


David McRitchie said:
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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.
Click to expand...
Click to expand...
Click to expand...
 
R

RagDyer

AND 97 too.

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

RD

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

Ken Wright said:
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..................

David McRitchie said:
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Patrick Fredin said:
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.

--
Patrick


:

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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.
Click to expand...
Click to expand...
Click to expand...
 
D

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.


David said:
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Patrick Fredin said:
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.

--
Patrick


David McRitchie said:
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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.
Click to expand...
Click to expand...
Click to expand...
 
D

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

David said:
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Patrick Fredin said:
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.

--
Patrick


David McRitchie said:
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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.
Click to expand...
Click to expand...
Click to expand...
 
D

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.
 
Top