Best way to create a petty cash worksheet/report?

[jim.thornton]

Does anyone have an idea how to create a report for petty cash?

I would like Sheet # 1 to be the report that has a dropdown at the top. Well, actually two. One for the Month and one for the Year. Or, one dropdown that has the month & year listed in it.

Sheet # 2
This will just be a sheet that has all of the transactions on it.

When the user opens up the workbook they will enter the transactions in sheet two.

Then, whenever they want a monthly report, they will go to sheet # 1 and select the dropdown for the appropriate month (or month and year -- two dropdowns). Then, when the month/year is selected the transactions on the report will be pulled from sheet # 2 and the total will be at the bottom of the transactions.

Any ideas on the best way to get the report pulled from sheet # 2?

 

[Misca]

I'd start with a simple pivot table. They're great for slicing and dicing the data any way you want to.

If you're not familiar with Pivot tables you can find a ton of great tutorials from YouTube.

 

[jim.thornton]

But with a pivot table I can't have a drop down and select the month and pull in the data just from that month can I?

 

[Misca]

The page filers are drop downs but in Pivot Tables they're always right above the actual table.

But if slicers are ok, those you can place anywhere. You can use them for all the same things as drop downs and you can control their looks & behavior more than you'd think.

And instead of just slicers you can use Timelines as well. They allow you to use not just years and months but the whole time hierarchy if you want to.

And if you take advantage of the Power Pivot data model as well you can't even imagine the things you can do with your data.

 

[footoo]

Could be done with one sheet only:

Col A : Day
Col B : Month
Col C : Year
Col D : Amount

Assuming the data is in rows 2 : 100, put in D102 : =SUBTOTAL(9,D2:D100)

Add a filter to columns B:C