Excel 2007 : Help with an expenses tracker

Hi there

I've adapted a sheet to use as an expenses tracker for my business.

I've attached the sheet if anyone is able to help. What I'm aiming to do is have all the months in seperate tabs (so far I have P01, P02 etc). In this I want to be able to give each payment I make from the business an id, allocate it to a department and then payment amount. This is on each sheet.

The stumbling block on this part is that all the payment departments do not show on the drop down menu. In the tab named settings I have listed all the departments I want to be able to choose from, but more than half of these are missing in this list. Can anyone instruct how I include the others in this list.

Secondly,

In the table called overview I have listed all the months (periods). I want to be able to have a tracker of the amounts for each period when it is chosen in a list. For example, if I put £230 in P01 for Staff Wages, it would auto update in P1 column on Overview. If I made a second payment in the same period for staff wages of £100, it would auto update in P01 in overview to a total of £330. Can anyone help with this?

In overview, the first column is Number of payments. I want this to be a rolling total of units for the whole year. For example, if I have 3 payments for staff wages in P1, 2 in P2 and 3 in P12 it would list this as 7 payments of this type for the year?

I really do appreciate anyones help on this. I understand that it's not easy and I'm struggling. If I could do this it would be much easier than my current pen and paper method.

Kind regards

John

Pullman Audit Trail.xls

On the first item(incomplete data validation list);
The Data Validation for these cells uses a NAMED range 'Departments'. That range was defined only to row 28. It needs to be defined out to row 56. On the Formulas tab, click on Name Manager, then find Departments from the list and change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

On the second item(summary report),
See the attach modified workbook.

For Number of Payments, I recommend using 'CountIF' functions in each month to give you subtotals in each category. This also gives you a brief monthly report. Then on the Overview sheet, just sum each category(12 months) subtotal.

Hi there

Thanks for this!

Really appreciate it.

Any chance you could just guide me through the CountIF feature?

Not 100% sure how to input that

Many thanks

John

Originally Posted by Dennis7849

On the first item(incomplete data validation list);
The Data Validation for these cells uses a NAMED range 'Departments'. That range was defined only to row 28. It needs to be defined out to row 56. On the Formulas tab, click on Name Manager, then find Departments from the list and change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

On the second item(summary report),
See the attach modified workbook.

For Number of Payments, I recommend using 'CountIF' functions in each month to give you subtotals in each category. This also gives you a brief monthly report. Then on the Overview sheet, just sum each category(12 months) subtotal.

Hi Dennis

Could you show me how to do this countif function?

Kind regards

John

See attached. It is done in Excel 2007.