OpenOffice formulas

[AngelBarb75]

Can anyone help me with this? I am trying to distribute amounts to other columns from one column based on its method of payment. For example. I have a column with the amount then I added one next to it to identify the method of payment (0=cash, 1=check 1; and 2=check 2). Then I have 3 columns labeled cash, check 1, check 2. I want the amount column to move it to its corresponding method pf payment. Can this be done?

 

[cjcobra]

I assume open office will be the same. If your amounts are in column A, and codes in Column B.

Column C = Cash, D = Check 1, E = Check 2

In Excel:

For Column C, use this formula to see if column B has a "0", if it does, then put the amount, if not, put a "0"
=if(b1=0,a1,0)

For Column D
=if(b1=1,a1,0)

For Column E
=if(b1=2,a1,0)

 

[Aladin Akyurek]

Can anyone help me with this? I am trying to distribute amounts to other columns from one column based on its method of payment. For example. I have a column with the amount then I added one next to it to identify the method of payment (0=cash, 1=check 1; and 2=check 2). Then I have 3 columns labeled cash, check 1, check 2. I want the amount column to move it to its corresponding method pf payment. Can this be done?

<TABLE border=0 rules=none cellSpacing=0 frame=void cols=5><COLGROUP><COL width=107><COL width=107><COL width=107><COL width=107><COL width=107></COLGROUP><TBODY><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" height=20 width=107 align=left>Value</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=left>MP</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=left>Cash</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=left>Check 1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=left>Check 2</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" height=20 align=right SDNUM="1033;" SDVAL="24">24</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="1">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="24">24</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=left>

</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" height=20 align=right SDNUM="1033;" SDVAL="35">35</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="0">0</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="35">35</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=left>

</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" height=20 align=right SDNUM="1033;" SDVAL="12">12</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="2">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="12">12</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" height=20 align=right SDNUM="1033;" SDVAL="45">45</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="1">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=left>

</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="45">45</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=left>

</TD></TR></TBODY></TABLE>

C2, just enter, copy across, and down:


=IF(SUMIF($H$1:$H$3;C$1;$I$1:$I$3)=$B2;$A2;"")

H1:I3 houses the symbols and the associated values:
<TABLE border=0 rules=none cellSpacing=0 frame=void cols=2><COLGROUP><COL width=107><COL width=107></COLGROUP><TBODY><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" height=20 width=107 align=left>Cash</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" width=107 align=right SDNUM="1033;" SDVAL="0">0</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" height=20 align=left>Check 1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="1">1</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" height=20 align=left>Check 2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid" align=right SDNUM="1033;" SDVAL="2">2</TD></TR></TBODY></TABLE>​

​

 

[AngelBarb75]

Hi guys, I tried both methods but I can't seem to get them to work. Cjcobra, when I enter your formula, it gives me an error. Aladin, I did the table exactly as you did, and entered the formula, rather, copied and pasted it... When I begin to enter values, the entire board shows the value; all columns and rows. As soon as I enter a number in MP column, they all disappear. On both maybe it is because it is OpenOffice...?

 

[AngelBarb75]

Aladin, I forgot to house the values as you did. I'm going to try it again.. thnx

 

[AngelBarb75]

Aladin, your way worked, thanks so much for your help. Complete life saver!

 

[Aladin Akyurek]

Aladin, your way worked, thanks so much for your help. Complete life saver!

You are welcome. Thanks for providing feedback.

 

[snoopyhr]

Just another approach, not sure if OpenOffice supports array in the same way or not, but here it is ....

Excel Workbook
	A	B	C	D	E
4	Amount	Group	Cash	Check 1	Check 2
5	100	0	100	*	*
6	200	2	*	*	200
7	300	1	*	300	*
8	400	2	*	*	400
9	500	1	*	500	*
Sheet1

<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C5</td><td >{=IF(ISERROR<span style=' color:008000; '>(INDEX<span style=' color:#0000ff; '>($A5,MATCH<span style=' color:#ff0000; '>($B5,COLUMN<span style=' color:#804000; '>(C2)</span>-3,0)</span>)</span>)</span>,"",INDEX<span style=' color:008000; '>($A5,MATCH<span style=' color:#0000ff; '>($B5,COLUMN<span style=' color:#ff0000; '>(C2)</span>-3,0)</span>)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'>Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by enteringformula with CTRL+SHIFT+ENTER!</span></td></tr>Copy across and down.... and confirmed with Ctrl+Shift+Enter.</table>