1. ## Excel reconciling function

I have a question that might be confusing - sorry... I have sheet that lists invoice records and the charge for a particular invoice. The vender is a clearing house, so when I receive stock, my one "order" might be supplied by 4 or 5 different venders. I have another sheet that I reconcile my charges on - so I have it set up that a formula that will match up the invioce numbers and charges on the second sheet, and total them up by invoice numbers on a 3rd sheet, subtracting from the total from the first sheet (there is probably an easier way to do this, but this is how I could work it out , - is there anyway that when the charge for a invoice number on the third sheet zero's out, that the invoice record on the first sheet will ie: turn red? Thanks in advance!

2. If you put a formula on sheet 1 which summed sheet3 values in each row with a invoice record, you could create a conditional format to read that formula and use it to color the cells. I would need an example to describe how to do it in more detail...

Steve

3. Thanks for the reply! I could supply an example tomorrow ( I would have to make a stand alone one)... and that would identify the record on one sheet (with a color etc) when the conditions had been met on the other? If so, way cool!

4. Trish,

Would you mean something like this?
Conditional Fmt using SUMIFS.JPG
The formula in the conditional formatting box is: =C2=SUMIFS(VendChgs,InvoiceNos,A2)

The names used in the formula define the InvoiceNo column {InvoiceNos} and the Charge column {VendChgs} both on the VendorCharges Sheet. These are fixed references but you should use Dynamic Range names {you can search here as there are several posts on the subject} so they will change as you add items.

Note: The Check column is merely there for visual verification that the formatting works and is not needed for the use in the conditional formatting.

5. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

trish12 (2012-02-10)

6. Sorry - I am a novice - I have played with your suggestions, but have not been successful - if you will indulge me, I have attached a sample - I complete the Expense Log - then reconcile the statement on the 2nd sheet - The 3rd sheet tracks what has been reconciled on the second sheet and zer0s out when the whole purchase has been received. Whenever a document number zeros out, I would like it to change the document number record on the expense log - i.e.: turn red... If possible can you offer any further advice?

7. Trish,

Based on your sample it was much simpler to accomplish. {see attached} Of course, I assumed that you are creating the table on the 3rd sheet. Look at the named range "Reconciled" as this is a Dynamic Range Name and will accommodate up to 5000 lines in the table. I hope this helps.

FYI: I'm originally from the Phila area and attended Pierce College {Pierce Jr. College then}.

8. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

trish12 (2012-02-10)

9. Thanks for the additional help! I have been in PHI for over 20 years, but I am a Gator

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•