Compare two sets of data

Nalex

Nalex

Board Regular
Joined
May 5, 2010
Messages
85
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I have two different sets of data, one from our payroll software and one from our billing software, and I need to compare them and highlight the differences. Each set of data has the following columns:
<o:p></o:p>
Billing Date / Hrs/Amt / Call Type / Client ID
<o:p></o:p>
I need to match up the Client ID, Billing Date, and Call Type and then compare the Hrs/Amt and highlight if different between the two sets of data. <o:p></o:p>

I would appreciate any ideas anyone has on this one. I started working with a Pivot Table to clean up the data as the current data sets can have multiple entries for each client and each date. From there I am kind of stumped.<o:p></o:p>

Thanks<o:p></o:p>
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Assuming you have both sets of data in different sheets.

You could add a column to both sets E.G

In the payroll Sheet header might be Billing Hrs/Amt and
In the billing Sheet the header might be Payroll Hrs/Amt

You need both to find entries in payroll that aren't in billing and visa versa.

You could then use INDEX/MATCH to pull the relevant values through.
Conditional formatting could highlight the rows where the values do not match.

An Example of the payroll sheet:

<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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Billing Date</td><td style=";">Hrs/Amt</td><td style=";">Call Type</td><td style=";">Client ID</td><td style=";">billing Hrs/Amt</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01/01/11</td><td style="text-align: right;;">1</td><td style=";">Error</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">02/01/11</td><td style="text-align: right;;">2</td><td style=";">Password</td><td style="text-align: right;;">10</td><td style=";">No Value Found</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">03/01/11</td><td style="text-align: right;;">3</td><td style=";">Password</td><td style="text-align: right;;">15</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">04/01/11</td><td style="text-align: right;;">4</td><td style=";">Stupidity</td><td style="text-align: right;;">20</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">05/01/11</td><td style="text-align: right;;">5</td><td style=";">Random</td><td style="text-align: right;;">25</td><td style="text-align: right;;">5</td></tr></tbody></table><p style="width:4.2em;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">payroll</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>Array 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">E2</th><td style="text-align:left">{=IF(<font color="Blue">ISNA(<font color="Red">INDEX(<font color="Green">billing!$B$2:$B$6,MATCH(<font color="Purple">1,(<font color="Teal">billing!$A$2:$A$6=payroll!A2</font>)*(<font color="Teal">billing!$C$2:$C$6=payroll!C2</font>)*(<font color="Teal">billing!$D$2:$D$6=payroll!D2</font>),0</font>)</font>)</font>),"No Value Found",INDEX(<font color="Red">billing!$B$2:$B$6,MATCH(<font color="Green">1,(<font color="Purple">billing!$A$2:$A$6=payroll!A2</font>)*(<font color="Purple">billing!$C$2:$C$6=payroll!C2</font>)*(<font color="Purple">billing!$D$2:$D$6=payroll!D2</font>),0</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">{=IF(<font color="Blue">ISNA(<font color="Red">INDEX(<font color="Green">billing!$B$2:$B$6,MATCH(<font color="Purple">1,(<font color="Teal">billing!$A$2:$A$6=payroll!A3</font>)*(<font color="Teal">billing!$C$2:$C$6=payroll!C3</font>)*(<font color="Teal">billing!$D$2:$D$6=payroll!D3</font>),0</font>)</font>)</font>),"No Value Found",INDEX(<font color="Red">billing!$B$2:$B$6,MATCH(<font color="Green">1,(<font color="Purple">billing!$A$2:$A$6=payroll!A3</font>)*(<font color="Purple">billing!$C$2:$C$6=payroll!C3</font>)*(<font color="Purple">billing!$D$2:$D$6=payroll!D3</font>),0</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">{=IF(<font color="Blue">ISNA(<font color="Red">INDEX(<font color="Green">billing!$B$2:$B$6,MATCH(<font color="Purple">1,(<font color="Teal">billing!$A$2:$A$6=payroll!A4</font>)*(<font color="Teal">billing!$C$2:$C$6=payroll!C4</font>)*(<font color="Teal">billing!$D$2:$D$6=payroll!D4</font>),0</font>)</font>)</font>),"No Value Found",INDEX(<font color="Red">billing!$B$2:$B$6,MATCH(<font color="Green">1,(<font color="Purple">billing!$A$2:$A$6=payroll!A4</font>)*(<font color="Purple">billing!$C$2:$C$6=payroll!C4</font>)*(<font color="Purple">billing!$D$2:$D$6=payroll!D4</font>),0</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">{=IF(<font color="Blue">ISNA(<font color="Red">INDEX(<font color="Green">billing!$B$2:$B$6,MATCH(<font color="Purple">1,(<font color="Teal">billing!$A$2:$A$6=payroll!A5</font>)*(<font color="Teal">billing!$C$2:$C$6=payroll!C5</font>)*(<font color="Teal">billing!$D$2:$D$6=payroll!D5</font>),0</font>)</font>)</font>),"No Value Found",INDEX(<font color="Red">billing!$B$2:$B$6,MATCH(<font color="Green">1,(<font color="Purple">billing!$A$2:$A$6=payroll!A5</font>)*(<font color="Purple">billing!$C$2:$C$6=payroll!C5</font>)*(<font color="Purple">billing!$D$2:$D$6=payroll!D5</font>),0</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">{=IF(<font color="Blue">ISNA(<font color="Red">INDEX(<font color="Green">billing!$B$2:$B$6,MATCH(<font color="Purple">1,(<font color="Teal">billing!$A$2:$A$6=payroll!A6</font>)*(<font color="Teal">billing!$C$2:$C$6=payroll!C6</font>)*(<font color="Teal">billing!$D$2:$D$6=payroll!D6</font>),0</font>)</font>)</font>),"No Value Found",INDEX(<font color="Red">billing!$B$2:$B$6,MATCH(<font color="Green">1,(<font color="Purple">billing!$A$2:$A$6=payroll!A6</font>)*(<font color="Purple">billing!$C$2:$C$6=payroll!C6</font>)*(<font color="Purple">billing!$D$2:$D$6=payroll!D6</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

<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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Billing Date</td><td style=";">Hrs/Amt</td><td style=";">Call Type</td><td style=";">Client ID</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01/01/11</td><td style="text-align: right;;">1</td><td style=";">Random</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">03/01/11</td><td style="text-align: right;;">3</td><td style=";">Password</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">04/01/11</td><td style="text-align: right;;">6</td><td style=";">Stupidity</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">05/01/11</td><td style="text-align: right;;">5</td><td style=";">Random</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">01/01/11</td><td style="text-align: right;;">2</td><td style=";">Error</td><td style="text-align: right;;">5</td></tr></tbody></table><p style="width:4.2em;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">billing</p><br /><br />
 
Upvote 0
Comfy,

Thanks so much for your reply... I like this solution, but I am worried that because in one of the data sets (the Billing set) i have multiple entries for the same day, this formula won't work.. For example, the billing set lists the same day many times for the same client, while the Payroll data will list it once with a total for the day.

For Example:

Excel 2007 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BACKGROUND: white; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid #A6AAB6 .75pt; mso-yfti-tbllook: 1184; mso-padding-alt: 1.5pt 1.5pt 1.5pt 1.5pt; mso-border-insideh: .75pt solid #A6AAB6; mso-border-insidev: .75pt solid #A6AAB6" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0><THEAD><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt"></TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt">
A<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt" colSpan=2>
B<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt">
C<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt">
D<o:p></o:p>

​
</TD></TR></THEAD><TBODY><TR style="mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
1<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
Billing Date<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt" colSpan=2>
Hrs/Amt<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
Call Type<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
Client ID<o:p></o:p>

​
</TD></TR><TR style="mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
2<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
08/01/11<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt" colSpan=2>
1<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
APC<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
001<o:p></o:p>

​
</TD></TR><TR style="mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
3<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
08/01/11<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt" colSpan=2>
2<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
APC<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
001<o:p></o:p>

​
</TD></TR><TR style="mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
4<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
08/01/11<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt" colSpan=2>
3<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
APC<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
001<o:p></o:p>

​
</TD></TR><TR style="mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; WIDTH: 105pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt" width=140 colSpan=3>
Billing<o:p></o:p>​


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


Excel 2007 <o:p></o:p>
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BACKGROUND: white; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid #A6AAB6 .75pt; mso-yfti-tbllook: 1184; mso-padding-alt: 1.5pt 1.5pt 1.5pt 1.5pt; mso-border-insideh: .75pt solid #A6AAB6; mso-border-insidev: .75pt solid #A6AAB6" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0><THEAD><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt"></TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt">
A<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt" colSpan=2>
B<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt">
C<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt">
D<o:p></o:p>

​
</TD></TR></THEAD><TBODY><TR style="mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
1<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
Billing Date<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt" colSpan=2>
Hrs/Amt<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
Call Type<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
Client ID<o:p></o:p>

​
</TD></TR><TR style="mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
2<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
08/01/11<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt" colSpan=2>
6<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
APC<o:p></o:p>

​
</TD><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt">
001<o:p></o:p>

​
</TD></TR><TR style="mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #a6aab6 1pt solid; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; WIDTH: 105pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #a6aab6 1pt solid; PADDING-TOP: 1.5pt; mso-border-alt: solid #A6AAB6 .75pt; mso-border-top-alt: solid #A6AAB6 .75pt" width=140 colSpan=3>
Payroll<o:p></o:p>​


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

And yes, the data is on 2 different sheets in the workbook. Sorry for my lack of knowledge her.. what do you think and thanks again
 
Upvote 0
In that case you can use SUMIFS in the payroll sheet to sum all Hrs that match the billing dat, Call Type and Client ID.

<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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Billing Date</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">Hrs/Amt</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">Call Type</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">Client ID</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">billing Hrs/Amt</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #FFFFFF;;">01/01/11</td><td style="text-align: right;background-color: #FFFFFF;;">1</td><td style="background-color: #FFFFFF;;">Error</td><td style="text-align: right;background-color: #FFFFFF;;">5</td><td style=";">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #FFFFFF;;">02/01/11</td><td style="text-align: right;background-color: #FFFFFF;;">2</td><td style="background-color: #FFFFFF;;">Password</td><td style="text-align: right;background-color: #FFFFFF;;">10</td><td style=";">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;background-color: #FFFFFF;;">03/01/11</td><td style="text-align: right;background-color: #FFFFFF;;">3</td><td style="background-color: #FFFFFF;;">Password</td><td style="text-align: right;background-color: #FFFFFF;;">15</td><td style=";">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #FFFFFF;;">04/01/11</td><td style="text-align: right;background-color: #FFFFFF;;">4</td><td style="background-color: #FFFFFF;;">Stupidity</td><td style="text-align: right;background-color: #FFFFFF;;">20</td><td style=";">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">05/01/11</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">5</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">Random</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">25</td><td style=";">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;">6</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">APC</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;">1</td><td style="text-align: right;;">6</td></tr></tbody></table><p style="width:4.2em;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">Payroll</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">E2</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A2,Billing!$C$1:$C$4,Payroll!C2,Billing!$D$1:$D$4,Payroll!D2</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A2,Billing!$C$1:$C$4,Payroll!C2,Billing!$D$1:$D$4,Payroll!D2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A3,Billing!$C$1:$C$4,Payroll!C3,Billing!$D$1:$D$4,Payroll!D3</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A3,Billing!$C$1:$C$4,Payroll!C3,Billing!$D$1:$D$4,Payroll!D3</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A4,Billing!$C$1:$C$4,Payroll!C4,Billing!$D$1:$D$4,Payroll!D4</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A4,Billing!$C$1:$C$4,Payroll!C4,Billing!$D$1:$D$4,Payroll!D4</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A5,Billing!$C$1:$C$4,Payroll!C5,Billing!$D$1:$D$4,Payroll!D5</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A5,Billing!$C$1:$C$4,Payroll!C5,Billing!$D$1:$D$4,Payroll!D5</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A6,Billing!$C$1:$C$4,Payroll!C6,Billing!$D$1:$D$4,Payroll!D6</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A6,Billing!$C$1:$C$4,Payroll!C6,Billing!$D$1:$D$4,Payroll!D6</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E7</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A7,Billing!$C$1:$C$4,Payroll!C7,Billing!$D$1:$D$4,Payroll!D7</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A7,Billing!$C$1:$C$4,Payroll!C7,Billing!$D$1:$D$4,Payroll!D7</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Billing Date</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Hrs/Amt</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Call Type</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Client ID</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">APC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">APC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">APC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td></tr></tbody></table><p style="width:4.2em;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">Billing</p><br /><br />

After posting have just thought of an issue with the conditional formatting when checking Billing for entries in Payroll.
The formula for the CF will need to be a sumif aswell. Will post an example after I have had a think, it's way to early to function.
 
Last edited:
Upvote 0
Possible conditional formatting rule for the billing sheet.

Code: 
=IF(SUMIFS($B$2:$B$4,$A$2:$A$4,$A$2,$C$2:$C$4,$C$2,$D$2:$D$4,$D$2)<>$E$2,1,0)

Colours don't copy but these cells are Red :)

<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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Billing Date</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Hrs/Amt</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Call Type</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Client ID</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Payroll Hrs/Amt</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">APC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-left: 1px solid black;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">APC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">1</td><td style="text-align: right;border-left: 1px solid black;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">APC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">1</td><td style="text-align: right;border-left: 1px solid black;;">6</td></tr></tbody></table><p style="width:4.2em;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">Billing</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FF0000" ><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: #FF0000;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">E2</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A2,Payroll!$C$1:$C$7,Billing!C2,Payroll!$D$1:$D$7,Billing!D2</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A2,Payroll!$C$1:$C$7,Billing!C2,Payroll!$D$1:$D$7,Billing!D2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A3,Payroll!$C$1:$C$7,Billing!C3,Payroll!$D$1:$D$7,Billing!D3</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A3,Payroll!$C$1:$C$7,Billing!C3,Payroll!$D$1:$D$7,Billing!D3</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A4,Payroll!$C$1:$C$7,Billing!C4,Payroll!$D$1:$D$7,Billing!D4</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A4,Payroll!$C$1:$C$7,Billing!C4,Payroll!$D$1:$D$7,Billing!D4</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:
Upvote 0
Thanks Comfy,

I will work on what you gave me and look for your next post.

Thanks
 
Upvote 0
Give this a go.

<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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Billing Date</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">Hrs/Amt</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">Call Type</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">Client ID</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">billing Hrs/Amt</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #FF0000;;">01/01/11</td><td style="text-align: right;background-color: #FF0000;;">1</td><td style="background-color: #FF0000;;">Error</td><td style="text-align: right;background-color: #FF0000;;">5</td><td style="background-color: #FF0000;;">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #FF0000;;">02/01/11</td><td style="text-align: right;background-color: #FF0000;;">2</td><td style="background-color: #FF0000;;">Password</td><td style="text-align: right;background-color: #FF0000;;">10</td><td style="background-color: #FF0000;;">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;background-color: #FF0000;;">03/01/11</td><td style="text-align: right;background-color: #FF0000;;">3</td><td style="background-color: #FF0000;;">Password</td><td style="text-align: right;background-color: #FF0000;;">15</td><td style="background-color: #FF0000;;">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #FF0000;;">04/01/11</td><td style="text-align: right;background-color: #FF0000;;">4</td><td style="background-color: #FF0000;;">Stupidity</td><td style="text-align: right;background-color: #FF0000;;">20</td><td style="background-color: #FF0000;;">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FF0000;;">05/01/11</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FF0000;;">5</td><td style="border-bottom: 1px solid black;background-color: #FF0000;;">Random</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FF0000;;">25</td><td style="background-color: #FF0000;;">No Entries Found</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;">6</td><td style="border-top: 1px solid black;background-color: #FFFFFF;;">APC</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;">1</td><td style="text-align: right;;">6</td></tr></tbody></table><p style="width:4.2em;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">Payroll</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">E2</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A2,Billing!$C$1:$C$4,Payroll!C2,Billing!$D$1:$D$4,Payroll!D2</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A2,Billing!$C$1:$C$4,Payroll!C2,Billing!$D$1:$D$4,Payroll!D2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A3,Billing!$C$1:$C$4,Payroll!C3,Billing!$D$1:$D$4,Payroll!D3</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A3,Billing!$C$1:$C$4,Payroll!C3,Billing!$D$1:$D$4,Payroll!D3</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A4,Billing!$C$1:$C$4,Payroll!C4,Billing!$D$1:$D$4,Payroll!D4</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A4,Billing!$C$1:$C$4,Payroll!C4,Billing!$D$1:$D$4,Payroll!D4</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A5,Billing!$C$1:$C$4,Payroll!C5,Billing!$D$1:$D$4,Payroll!D5</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A5,Billing!$C$1:$C$4,Payroll!C5,Billing!$D$1:$D$4,Payroll!D5</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A6,Billing!$C$1:$C$4,Payroll!C6,Billing!$D$1:$D$4,Payroll!D6</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A6,Billing!$C$1:$C$4,Payroll!C6,Billing!$D$1:$D$4,Payroll!D6</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E7</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A7,Billing!$C$1:$C$4,Payroll!C7,Billing!$D$1:$D$4,Payroll!D7</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Billing!$B$1:$B$4,Billing!$A$1:$A$4,Payroll!A7,Billing!$C$1:$C$4,Payroll!C7,Billing!$D$1:$D$4,Payroll!D7</font>)</font>)</td></tr></tbody></table></td></tr></table><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>Conditional Formatting 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">Refers To</th><th style="text-align:left">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">$A$2:$E$7</th><td style="text-align:left">=$B2<>$E2</td></tr></tbody></table></td></tr></table><br />

<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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Billing Date</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Hrs/Amt</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Call Type</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Client ID</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Payroll Hrs/Amt</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">APC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">APC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">08/01/11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">APC</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1</td><td style="text-align: right;border-left: 1px solid black;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #FF0000;;">40547</td><td style="text-align: right;background-color: #FF0000;;">3</td><td style="background-color: #FF0000;;">Stupidity</td><td style="text-align: right;background-color: #FF0000;;">20</td><td style="text-align: right;background-color: #FF0000;;">4</td></tr></tbody></table><p style="width:4.2em;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">Billing</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">E2</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A2,Payroll!$C$1:$C$7,Billing!C2,Payroll!$D$1:$D$7,Billing!D2</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A2,Payroll!$C$1:$C$7,Billing!C2,Payroll!$D$1:$D$7,Billing!D2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A3,Payroll!$C$1:$C$7,Billing!C3,Payroll!$D$1:$D$7,Billing!D3</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A3,Payroll!$C$1:$C$7,Billing!C3,Payroll!$D$1:$D$7,Billing!D3</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A4,Payroll!$C$1:$C$7,Billing!C4,Payroll!$D$1:$D$7,Billing!D4</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A4,Payroll!$C$1:$C$7,Billing!C4,Payroll!$D$1:$D$7,Billing!D4</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=IF(<font color="Blue">SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A5,Payroll!$C$1:$C$7,Billing!C5,Payroll!$D$1:$D$7,Billing!D5</font>)=0,"No Entries Found",SUMIFS(<font color="Red">Payroll!$B$1:$B$7,Payroll!$A$1:$A$7,Billing!A5,Payroll!$C$1:$C$7,Billing!C5,Payroll!$D$1:$D$7,Billing!D5</font>)</font>)</td></tr></tbody></table></td></tr></table><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>Conditional Formatting 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">Refers To</th><th style="text-align:left">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">$A$2:$E$5</th><td style="text-align:left">=SUMIFS(<font color="Blue">$B$2:$B$5,$A$2:$A$5,$A2,$C$2:$C$5,$C2,$D$2:$D$5,$D2</font>)<>$E2</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Comfy,

everythingis working great except for the conditional formating.... I cant seem to get this to work, and I am sure it is something I am doing wrong. My problem is that when I put the formula you gave me into the formula bar for the conditional formating, and I change the range to 1000 (to include all my data) it only highlights the last 2 rows in the range (rown 999 and 1000)... Any thoughts?

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Comparing Columns of names
Replies
5
Views
267
Zot
Z
S
  • Question Question
Macro to compare two sheets with identical column headers and first column, then showing all table differences in third sheet
Replies
1
Views
279
DanteAmor
DanteAmor
J
  • Question Question
Differentiating between clients for a specific recap
Replies
6
Views
123
January1991
J
D
  • Question Question
i need a excel macro to read from several pdf file kept under one folder and put in on a single excel output file
Replies
1
Views
465
ExcelToDAX
E
R
  • Question Question
Checking Large Data table for text matched in a row to be selected by an id
Replies
1
Views
332
RobP
R

Forum statistics

Threads
1,224,590
Messages
6,179,750
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top