Subscription cost summary

[needinghelp2765]

Hi there,

I’m trying to create a spreadsheet to keep track of user subscriptions across the company, and the costs for each department.

I’ve set up a dummy template (Dropbox via link) and am looking for some help with the following;

1. The sum of costs per annum will pull across from all subscription tabs to show the totals on the summary tab, based on the department allocated to the user (not sure if I need another step here to show all total user costs p/a?)
2. I need to continue to add in more tabs - is there a way to set up the formula on the summary so that it's pulling the totals based on the tab name? Or is it better to set up say 100 template tabs with all formulas set up and just hide the rows until I need to use?
3. To make things difficult (for me anyway!), some users may be split across different departments e.g. 50% to Corporate and 50% to Administration. Is there a way to set up this split for the annual fees? Or is it better to create duplicate lines for the user in each subscription and manually change the amount and department allocation for each?

Any help would be greatly appreciated!

Dropbox

www.dropbox.com

 

[bobsan42]

First, foremost and ALWAYS avoid splitting data of the same type across different sheets/tabs. But only if you want to make your life easier.
Keep it in a data table together and then making a summary of it is pretty simple and easy.
I would not stick a price to the subscription - it may change next year and then you'll have a lot of problems solving this.
Keep it as simple as possible - don't try to make a "fully functioning app" if not necessary e.g. do you really need to make a list of departments, subscriptions, users etc. (well, maybe for users and titles you can make a separate list but then it should all go to one master table).
Especially if the data flow is not too big. How many entries would you expect per month or per year?
Unless you feel very enthusiastic
I would make it like this:

then a summary of such data is a piece of cake.