Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #3
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts
    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. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-02-08 at 18:46.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    trish12 (2012-02-10)

  6. #5
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts
    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?
    Attached Files Attached Files

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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}.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    trish12 (2012-02-10)

  9. #7
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts
    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
  •