Running total on expense sheet: Subtract and Transpose values?

[mborschm]

Hi All,

I run craft workshops and have to purchase the supplies myself and am then reimbursed by the company. I want to set up a spreadsheet to keep track of my expenses and what I have already been reimbursed for. I often buy in bulk to save on costs.
What I'd like to be able to do is for Excel to recognise when I'm using stock I've already paid for.
So for example:

Workshop A:

Item	Price (Total)	Units	Price per unit	$ reimbursed for	$ owing
Yarn	$20	10	$2	$12	$8

So after workshop A, I still have 4 balls of yarn left for the next workshop, totalling $8.

If I then do a second workshop using up the yarn I already have, how can I tell excel to reduce the money I'm owed? So the next row would show totals like this:
Workshop B:

Item	Price (Total)	Units	Price per unit	$ reimbursed for	$ owing
Yarn	$0? (since I have already paid for it)	4 (remaining from the original purchase)	$2	$20 (now reimbursed the full amount)	$0

Is this very complicated? I imagine it would be something like =IF the name of the product is exactly the same to run a series of calculations but I can't figure out how to tell it to do what I want.

Thanks!!

 

[jtakw]

Hi,

I think for your spreadsheet to be more "effective", you'll need to include other factors, and as you progress, there may be more items you'll want included, but this is a start:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Workshop A:
2	Item	Price (Total)	Units Purchased	Units on Hand	Units Total	Price per unit	Units Used	$ reimbursed for	$ owing
3	Yarn	$20	10	0	10	$2	6	$12	$8
4
5	Workshop B:
6	Item	Price (Total)			Units	Price per unit		$ reimbursed for	$ owing
7	Yarn	$0	0	4	4	$2	4	$8	$0
Sheet1075

Cell Formulas
Range	Formula
B3,B7	B3	=C3*F3
E3,E7	E3	=C3+D3
H3,H7	H3	=F3*G3
I3	I3	=B3-H3
D7	D7	=E3-G3
I7	I7	=SUM(B3:B7)-SUM(H3:H7)