Nov 03 2021 01:38 PM
Nov 03 2021 01:38 PM
I need to keep track of running balance for purchase orders. My spreadsheet contains columns for:
|Vendor||Purchase Order #||Original AMT PO Funding||CURRENT BALANCE||PURCHASE DATE||Time||Authorization #||Purchase Amount||PO FUND Running Balance||School Name or Office||Work Order #||Mechanic Name & Trade||STATUS|
I need to find the most recent vendor purchase to determine the running balance. The vendors are in a dropdown and once selected vlookup supplies the po#, original amt po funded. Once a purchase is made it deducts the amount from the current balance. I need the current balance to be kept on another sheet so that it will automatically update after each purchase (the next purchase from that vendor). Is there a string of functions that will allow me to accomplish that?
Nov 04 2021 01:13 PM
I don't have that information and will have to create fake data to test.
I've also discovered a glitch in my vlookup function. If I use $ to specify then the next row is not correct. I.E., =VLOOKUP(A4,REFERENCE!$A$32:$C$32,2,FALSE) and =VLOOKUP(A4,REFERENCE!$A$32:$C$32,3,FALSE)
How do I use that formula to specifically identify the value in column A? In the Reference worksheet "Sample Entry" is in row 32, however, "ABC Supply" is on row 3 of column A. Which means that I can't just copy the VLOOKUP down to the next row in columns B and C because that will on reference $A$32:$C:$32. It needs to reference the vendor by name and specific row for that vendor in order for the data to be correct. I thought I had the vlookup ready to run.
Nov 04 2021 03:12 PM
I created sample data for your worksheet:
If you used VLOOKUP(), it will only return the first instance it will find in the POLOG sheet, however if you used this formula:
it will find all the invoice/purchase request related to the PO and sum them all up.
Notice in Fig.1 above the column that says count, some POs have more than one expenditure entries which were all summed up to show the actual current balance of the respective POs.