Format overlapping cells

[sythong]

Greetings to my excel pals again. Need help
on the following worksheet format which is
prepared by a non-excel user:

A BC DE
1 RM100 RM100

Column A shows Qty
Column BC is merge to show RM100
Column DE is merged to show RM100

Actually 3 columns would suffice, i.e.
Col A is for qty, B for unit price and
C for Sub-Total. For information purpose
the above merged format is ok but now
I am unable to do any calculation on this.

How can I untangle this so as to carry out
calculations.


Thanks

Thong

 

[RAM]

Why is it merged? What would happen if you unmerge it?

RAM

 

[Peter_SSs]

How can I untangle this so as to carry out
calculations

What calculations are you trying to do that don't work with the merged cells? Perhaps you could give us a bit more sample data and what you are trying to do with it?

 

[sythong]

RAM


I think the person doing the typing did
not envisage that it would be used for calculation.
Instead it was more of an aesthetic purpose.
I think I should have unmerged the cells!!!


Peter
I am trying to total up all the data but because the
cells are merged I am not sure how I can sum
the merged cell. In addition the RM sign is not
done throuch cell formatting but type in.

Anyway I will unmerged the cells and reformat
it.

Thanks

 

[Peter_SSs]

sythong

I am not suggesting that you leave the cells merged, but I believe you CAN if you want. I also assume that it is only the Quantity and unit price that is typed in and that the sub-total could be calculated.

See if this is any help.

Columns B and C are merged, as are columns D and E.
Formula in merged cell D1:E1 (copied down to row 5) is:
=A1*SUBSTITUTE(B1,"RM","")

Formula in merged cell D6:E6 is:
=SUM(D1:E5)

Mr Excel.xls
	A	B	C	D	E
1	1	RM100		100.00
2	3	RM50		150.00
3	4	RM100		400.00
4	1	RM200		200.00
5	3	RM50.50		151.50
6		Total		1001.50
Sheet1

 

[RAM]

I haven't found merged cells recommended by anyone on this board that really knows Excel, but I found that I sometimes need them to make my forms look decent. They are sometimes a drag when you reformat your forms when you under some conditions need to unmerge the cells and merge them back again and VBA will not work with merged cells.

I can probably recommend you merge as little cells as possible.

Edit: Peter gave you a good solution to keep them in this case.

Edit 2: If you want to place the RM in one column and the cost in another you can use Data - Text to Columns, select Fixt Width (in this case).

HTH

RAM

 

[Peter_SSs]

Edit 2: If you want to place the RM in one column and the cost in another you can use Data - Text to Columns, select Fixt Width (in this case).

Or, to get rid of the RM altogether, select column B then Edit|Replace...|Find what: RM|Replace with:
then click Replace All

 

[RAM]

That's right. You can even do the same (delete RM) using Data - Text to Columns. In the third step select RM, select Do not import (skip) and Finish.

I haven't seen your form, but I guess I would keep RM in one column, right align, and the cost, left align, in another. It keeps the formulas simple.

Hope you get your form together.

RAM

 

[RAM]

I just found another topic that will probably be the best solution for you sythong: http://www.mrexcel.com/board2/viewtopic.php?t=182243

Use a Custom format and don’t enter the RM part, just the cost and the RM will prefix the cost automatically. Then use the formulas you normally use.

Follow the step in the above topic and you have a solution. Select all the cells with RM and then do the Custom Format. If you later want to copy that format use the Format Painter.

HTH

RAM