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.

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.

SSParks

THIS IS SO COOOL! IT WORKED!

I cant thank you enough you really broke it down where I could understand it! I appreciate you taking the time to do that. I have it working based off what you placed above and this will help take SS to a higher level at the park district. Im going to keep going tomorrow and follow up if I have any questions. Thank you! MD

Julie Fortney

That is awesome to hear! If you haven't already, you could build workflows to alert someone when the amount remaining on a PO is below a set threshold, create dashboards to display the information, etc. So many possibilities!

SSParks

Is that an easy explanation on how to do the amount remaining threshold? Example, $1,000 or less left on a PO that started with $10,000? I see you can do a workflow when column or cell is = to an amount but can we do less then $1,000? Thanks for any info!

Julie Fortney

I would use a checkbox column with a formula, which will give you the ability to set different parameters if needed. For instance, your formula could say if the amount remaining/PO Amount is less than 10%, check the box. Then set that checkbox as the workflow trigger.

SSParks

Interesting! I didn't know you could formula to select a checkbox if criteria has been met. Ill research that thank you!

Julie Fortney

Yes, it's a great function - you'll probably start finding a lot of uses for it!