New Contributor

# help with functions

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 PENDING COMPLETE RECEIPT

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?

4 Replies

# Re: help with functions

Do you have a sample of 20 POs with varied purchases that can be tested? Your sample file is sparse for testing, but here's a sample:

# Re: help with functions

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.

# Re: help with functions

=VLOOKUP(A4,REFERENCE!\$A\$2:\$C\$32,2,FALSE)

# Re: help with functions

I created sample data for your worksheet:

Fig. 1

If you used VLOOKUP(), it will only return the first instance it will find in the POLOG sheet, however if you used this formula:

=IFERROR(SUM(FILTER(POLOG!\$I\$4:\$I\$42,ISNUMBER(FIND(\$B3,POLOG!\$B\$4:\$B\$42)))),"")

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.

cheers