Need help with conditional formatting formula!

[CollapsedSilence]

Hello,

New to the forums, but the knowledge here is amazing. Defintely will pop my head in and offer any advice I can to others.

I've been banging my head against a wall trying to find a way to make this work, and with no success..


I'm trying to have a formula in the empty column that will check my subtotal Net DR/CR's; if it nets 0.00, I want a X next to all the Bursar Batch #'s that are the same. If it does not net 0.00 I want a Y next to all the bursar batch #'s.

So for instance, since "16268 Total" nets 0.00, I want rows 2-5 to have an "X" in that blank colum. Since "16270 Total" nets to (4050.00) I want rows 8-9 to have a "Y" in the blank column.

Thanks in advance for trying to help, I appreciate any brainstorming people can come up with.

 

[CollapsedSilence]

Here is a link to the fullsize image if that helps.
http://imageshack.us/photo/my-images/688/excelhelp.png/

 

[CollapsedSilence]

Anyone have thoughts/suggestions/ideas?

 

[Romi Kishore]

I think this doesn't need conditional formatting since you want to place a literal "X" or "Y" there.

I tried my hand at the formula and it's simple but it will change depending upon your column positioning. For the exact same picture as you pasted (assuming the Date column to be Excel column A, the formula for the Blank column (F) would be ...

=IF(ISNA(INDIRECT(CONCATENATE("C",MATCH(CONCATENATE(E2," Total"),E:E,0)))),"",IF(INDIRECT(CONCATENATE("C",MATCH(CONCATENATE(E2," Total"),E:E,0)))=0,"X","Y"))

Paste this formula on Excel row 2 and drag it down to wherever your data ends.

 

[konew1]

If the $ amounts are in column C:C and Burser code in E:E then put this in a blank column on row 2 and copy down.
IF(E2="","",IF(SUMIF(E:E,E2,C:C)=0,"y","x"))
It will create a code X oy Y in an adjacent column. You can then use that code for your conditional formatting. If you need help with conditional formatting then let us know

 

[CollapsedSilence]

Thank you much Romi! Whats simple to you is pretty complex to me; I definitely have a lot to learn.

Konew, thanks for the suggestion. Ill have to give it a try when I get in tomorrow but I definitely know between yours and Romi's idea I can get something to work.

 

[konew1]

My first reply will give an X or Y against every line with a bursers code that dont add to zero. If you just want the X or Y on the total lines then try this in column F, starting in F2 then copy down
=IF(Right(E2,5)<>"Total","",If(d2<>0),"Y","X")

 

[pedro-egoli]

This seems to work

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Desacription	et Dr/Cr	Set ID	Bursar Bar	*	Result
2	12/7/1011	Joey	36	123	16268	*	*
3	14-07-11	April	25	456	16268	*	*
4	02-07-11	Cash receipts	-61	457	16268	*	*
5	*	*	0.00	*	16268	Total	X
6	02-07-11	Receipts	-780	169	16269	*	*
7	*	*	-780	*	16269	Total	Y
8	02-07-11	Cash Receipts	-4050	180	16270	*	*
9	*	*	-4050	181	16270	Total	Y
Sheet1

Pedro

 

[Romi Kishore]

@konew1 ... both your formulas are amazingly shorter ... just too good!
@CollapsedSilence ... Yeah, I will be more cognizant and selective about the word "simple" in the future. Thanks for the eye-opener. It was my first reply on the forum.

 

[markmzz]

CollapsedSilence,

If I understand correctly what you want, then I think that this can help you:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #C5D9F1;;">Date</td><td style="background-color: #C5D9F1;;">Description</td><td style="background-color: #C5D9F1;;">Net DR/CR</td><td style="background-color: #C5D9F1;;">Set ID</td><td style="background-color: #C5D9F1;;">Bursar Batch #</td><td style="background-color: #C5D9F1;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">7/12/2011</td><td style=";">Decription01</td><td style="text-align: right;;">36.00 </td><td style=";">AD071211</td><td style="text-align: right;;">16268</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">7/14/2011</td><td style=";">Decription02</td><td style="text-align: right;;">25.00 </td><td style=";">AD071411</td><td style="text-align: right;;">16268</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">7/2/2011</td><td style=";">Decription03</td><td style="text-align: right;;">(61.00)</td><td style=";">G070111</td><td style="text-align: right;;">16268</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00 </td><td style="text-align: right;;"></td><td style="font-weight: bold;;">16268 Total</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">7/2/2011</td><td style=";">Decription04</td><td style="text-align: right;;">(780.00)</td><td style=";">G070111</td><td style="text-align: right;;">16269</td><td style="text-align: center;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">(780.00)</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">16269 Total</td><td style="text-align: center;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7/2/2011</td><td style=";">Decription05</td><td style="text-align: right;;">(4,050.00)</td><td style=";">G070111</td><td style="text-align: right;;">16270</td><td style="text-align: center;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">(4,050.00)</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">16270 Total</td><td style="text-align: center;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">7/14/2011</td><td style=";">Decription06</td><td style="text-align: right;;">3,000.00 </td><td style=";">AD071411</td><td style="text-align: right;;">16271</td><td style="text-align: center;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">7/14/2011</td><td style=";">Decription07</td><td style="text-align: right;;">100.00 </td><td style=";">AD071411</td><td style="text-align: right;;">16271</td><td style="text-align: center;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">7/21/2011</td><td style=";">Decription08</td><td style="text-align: right;;">10.00 </td><td style=";">AD072111</td><td style="text-align: right;;">16271</td><td style="text-align: center;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">7/2/2011</td><td style=";">Decription09</td><td style="text-align: right;;">(5,458.00)</td><td style=";">G070111</td><td style="text-align: right;;">16271</td><td style="text-align: center;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">(2,348.00)</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">16271 Total</td><td style="text-align: center;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">7/7/2011</td><td style=";">Decription10</td><td style="text-align: right;;">25.00 </td><td style=";">AD070711</td><td style="text-align: right;;">16272</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">7/7/2011</td><td style=";">Decription11</td><td style="text-align: right;;">100.00 </td><td style=";">AD070711</td><td style="text-align: right;;">16272</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">7/7/2011</td><td style=";">Decription12</td><td style="text-align: right;;">50.00 </td><td style=";">AD070711</td><td style="text-align: right;;">16272</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">7/7/2011</td><td style=";">Decription12</td><td style="text-align: right;;">6.00 </td><td style=";">AD070711</td><td style="text-align: right;;">16272</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">7/8/2011</td><td style=";">Decription13</td><td style="text-align: right;;">25.00 </td><td style=";">AD070811</td><td style="text-align: right;;">16272</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">7/14/2011</td><td style=";">Decription12</td><td style="text-align: right;;">50.00 </td><td style=";">AD071411</td><td style="text-align: right;;">16272</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">7/2/2011</td><td style=";">Decription14</td><td style="text-align: right;;">(256.00)</td><td style=";">G070111</td><td style="text-align: right;;">16272</td><td style="text-align: center;;">X</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00 </td><td style="text-align: right;;"></td><td style="font-weight: bold;;">16272 Total</td><td style="text-align: center;;">X</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">ISTEXT(<font color="Red">E$2:E$22</font>),IF(<font color="Red">ISTEXT(<font color="Green">E$2:E$22</font>)*(<font color="Green">C$2:C$22=0</font>),"X","Y"</font>),F3</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz