Excel Finance?

Question

hi,
Doing finances in excel, is there anyway of making a button that says PAID and UNPAID then removes the amount in that line from the sum of direct debits?

eg
Sky >click paid>> direct debit line removes sky amount and shows a new total of the others that are still unpaid

Answer 1

you could create a macro.

Answer 2

Simple way would be with formula.

Assume 5 columns (A to E).. Name, Cost(£), Paid (Y/N) ?, Due (£), Paid (£)

In the cost (B) column put the amount due per month.

The 'Paid (Y/N) Column (C) cells can be defined as 'drop down list' with choice of 2 'Y' and 'N' .. however it dosn't matter so long as you put a 'Y' in that column when its paid

The cells in 'Due(£)' (D) have the formula like
=IF(C2<>"Y",B2,0)

and the cells in Paid(£) (E) have the formula like
=IF(C2="Y",B2,0)

In above formula I'm assuming row 1 has the column titles and the first money entry is in row 2 (hence C2, B2) ..

When you copy & paste formula the row numbers will be automatically adjusted .. so get row 2 right and then copy to the rest of the rows.


NB you will see in above example that if you enter a lower case 'y' the formula will not detect that the bill has been paid - you can correct for this by adjusting the formulas as follows :-
for E, =IF(C2="Y" or C2="y",B2,0)

and for D, =IF(E2=0,B2,0)