Grid help needed to reference different POs with changing totals?

SSParks

Greetings-

I created a SS for my finance dept. Basic form where they can select a vendor from a drop down menu and enter that month's bill with what they paid. Each vendor has a different PO number with a different total amount on that particular PO (purchase order). Im having trouble figuring out how to start with a PO balance of $10,000 and subtract the paid bill for this month $1,000 for example. I basically need a running total of whats left on the PO? I assumed I could have a separate grid with the PO numbers and start totals listed to start but not sure how to reference that PO and subtract the amount thats been paid for that month. Thanks for any help/info! MD

Vendor

ATT - P0031379 - $10,000

Verizon - P0031380 - $5,000

Julie Fortney

Here's what I would do:

Create a tracking sheet with your PO information. I'm not sure if you might have more than one PO for each vendor, so I set this up to accommodate multiple POs for one vendor:

Columns 1, 2, and 4 are manual entry.

Vendor, PO Combined formula: =Vendor@row + " - " + [PO Number]@row

Amount Paid: You can sum the values entered for that vendor and PO in the Amount Paid column using a cross sheet formula. Here's my Amount Paid formula: =SUMIF({Finance Entry Vendor}, [Vendor, PO Combined]@row, {Finance Entry Amount Paid})

Here's the Finance Entry sheet:

I suggest using the Vendor, PO Combined column from your PO Tracking sheet to populate the Vendor dropdown, so even if you have multiple POs for one vendor you can match the amounts paid to the correct PO. If you have Data Shuttle it can continuously update this dropdown for you. :)

I'm not sure what your comfort level is with cross sheet formulas, so just let me know if you have additional questions.