help with functions

%3CLINGO-SUB%20id%3D%22lingo-sub-2923465%22%20slang%3D%22en-US%22%3Ehelp%20with%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2923465%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20keep%20track%20of%20running%20balance%20for%20purchase%20orders.%20My%20spreadsheet%20contains%20columns%20for%3A%3C%2FP%3E%3CTABLE%20width%3D%221701%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22196.953px%22%3EVendor%3C%2FTD%3E%3CTD%20width%3D%2293.9844px%22%3EPurchase%20Order%20%23%3C%2FTD%3E%3CTD%20width%3D%22103.984px%22%3E%26nbsp%3BOriginal%20AMT%20PO%20Funding%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22103.984px%22%3E%26nbsp%3BCURRENT%20BALANCE%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2293.9844px%22%3EPURCHASE%20DATE%3C%2FTD%3E%3CTD%20width%3D%2262.9844px%22%3ETime%3C%2FTD%3E%3CTD%20width%3D%2262.9844px%22%3EAuthorization%20%23%3C%2FTD%3E%3CTD%20width%3D%2278.9844px%22%3E%26nbsp%3BPurchase%20Amount%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2285.9844px%22%3E%26nbsp%3BPO%20FUND%26nbsp%3B%20Running%20Balance%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22235.938px%22%3ESchool%20Name%20or%20Office%3C%2FTD%3E%3CTD%20width%3D%2297.9844px%22%3EWork%20Order%20%23%3C%2FTD%3E%3CTD%20width%3D%22245.938px%22%3EMechanic%20Name%20%26amp%3B%20Trade%3C%2FTD%3E%3CTD%20width%3D%22236.312px%22%3ESTATUS%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2278.9844px%22%3EPENDING%3C%2FTD%3E%3CTD%20width%3D%2288.2188px%22%3ECOMPLETE%3C%2FTD%3E%3CTD%20width%3D%2269.1094px%22%3ERECEIPT%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EI%20need%20to%20find%20the%20most%20recent%20vendor%20purchase%20to%20determine%20the%20running%20balance.%20The%20vendors%20are%20in%20a%20dropdown%20and%20once%20selected%20vlookup%20supplies%20the%20po%23%2C%20original%20amt%20po%20funded.%20Once%20a%20purchase%20is%20made%20it%20deducts%20the%20amount%20from%20the%20current%20balance.%20I%20need%20the%20current%20balance%20to%20be%20kept%20on%20another%20sheet%20so%20that%20it%20will%20automatically%20update%20after%20each%20purchase%20(the%20next%20purchase%20from%20that%20vendor).%20Is%20there%20a%20string%20of%20functions%20that%20will%20allow%20me%20to%20accomplish%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2923465%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2924758%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2924758%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1206046%22%20target%3D%22_blank%22%3E%40camelia_eaves_2k21%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20a%20sample%20of%2020%20POs%20with%20varied%20purchases%20that%20can%20be%20tested%3F%20Your%20sample%20file%20is%20sparse%20for%20testing%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2928675%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2928675%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20have%20that%20information%20and%20will%20have%20to%20create%20fake%20data%20to%20test.%3C%2FP%3E%3CP%3EI've%20also%20discovered%20a%20glitch%20in%20my%20vlookup%20function.%20If%20I%20use%20%24%20to%20specify%20then%20the%20next%20row%20is%20not%20correct.%20I.E.%2C%26nbsp%3B%3DVLOOKUP(A4%2CREFERENCE!%24A%2432%3A%24C%2432%2C2%2CFALSE)%20and%26nbsp%3B%3DVLOOKUP(A4%2CREFERENCE!%24A%2432%3A%24C%2432%2C3%2CFALSE)%3C%2FP%3E%3CP%3EHow%20do%20I%20use%20that%20formula%20to%20specifically%20identify%20the%20value%20in%20column%20A%3F%20In%20the%20Reference%20worksheet%20%22Sample%20Entry%22%20is%20in%20row%2032%2C%20however%2C%20%22ABC%20Supply%22%20is%20on%20row%203%20of%20column%20A.%20Which%20means%20that%20I%20can't%20just%20copy%20the%20VLOOKUP%20down%20to%20the%20next%20row%20in%20columns%20B%20and%20C%20because%20that%20will%20on%20reference%20%24A%2432%3A%24C%3A%2432.%20It%20needs%20to%20reference%20the%20vendor%20by%20name%20and%20specific%20row%20for%20that%20vendor%20in%20order%20for%20the%20data%20to%20be%20correct.%20I%20thought%20I%20had%20the%20vlookup%20ready%20to%20run.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2928693%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2928693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1206046%22%20target%3D%22_blank%22%3E%40camelia_eaves_2k21%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DVLOOKUP(A4%2CREFERENCE!%24A%242%3A%24C%2432%2C2%2CFALSE)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2929239%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2929239%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1206046%22%20target%3D%22_blank%22%3E%40camelia_eaves_2k21%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20sample%20data%20for%20your%20worksheet%3A%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EFig.%201%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1636063508610.png%22%20style%3D%22width%3A%20680px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F324390i2D1309D0ED50D0AB%2Fimage-dimensions%2F680x425%3Fv%3Dv2%22%20width%3D%22680%22%20height%3D%22425%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1636063508610.png%22%20alt%3D%22Yea_So_0-1636063508610.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIf%20you%20used%20VLOOKUP()%2C%20it%20will%20only%20return%20the%20first%20instance%20it%20will%20find%20in%20the%20POLOG%20sheet%2C%20however%20if%20you%20used%20this%20formula%3A%3C%2FP%3E%3CP%3E%3DIFERROR(SUM(FILTER(POLOG!%24I%244%3A%24I%2442%2CISNUMBER(FIND(%24B3%2CPOLOG!%24B%244%3A%24B%2442))))%2C%22%22)%3C%2FP%3E%3CP%3Eit%20will%20find%20all%20the%20invoice%2Fpurchase%20request%20related%20to%20the%20PO%20and%20sum%20them%20all%20up.%3C%2FP%3E%3CP%3ENotice%20in%20Fig.1%20above%20the%20column%20that%20says%20count%2C%20some%20POs%20have%20more%20than%20one%20expenditure%20entries%20which%20were%20all%20summed%20up%20to%20show%20the%20actual%20current%20balance%20of%20the%20respective%20POs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I need to keep track of running balance for purchase orders. My spreadsheet contains columns for:

VendorPurchase Order # Original AMT PO Funding  CURRENT BALANCE PURCHASE DATETimeAuthorization # Purchase Amount  PO FUND  Running Balance School Name or OfficeWork Order #Mechanic Name & TradeSTATUS
PENDINGCOMPLETERECEIPT

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

@camelia_eaves_2k21 

 

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:

Yea_So_0-1635993033309.png

 

@Yea_So 

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.

 

@camelia_eaves_2k21 

 

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

@camelia_eaves_2k21 

 

I created sample data for your worksheet:

Fig. 1

Yea_So_0-1636063508610.png

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