Forum Discussion
Excel Template Help
bwk4s I made a start for you, but have to go do something else soon. I trust you'll manage to complete the last bit of formatting, should you decide to continue using this template.
Personally, I find such templates quite useless. They teach tricks about (sometimes) more complex functions. In this case, I believe the emphasis lies on INDEX, MATCH and RANK. But, in general, they are not very well constructed and difficult to adapt to ones real-life situation.
So, adding the debt summary involves quite a few manual steps. First, you need to add a column to the debt table, by which you want to categorise the different debt (that is if you don't want to show all 10 of them separately). But, then they already have a column called Category which in facts contains Items, if you compare it to the Asset table. I wonder why.
Then, they rank the totals by category in descending order just to reverse the order again to make the categories show up in the correct order for the chart. Sadly, the last step of re-ordering has hard-coded numbers in it. For the assets, there are 5 categories to sort, so the INDEX/MATCH functions in column U and V start at 5 and count down to 1. Now, I made an example with only 4 debt types, so the formulae in AE and AF count down from 4 to 1.
If you, in your own sheet, use more or fewer categories, you need to change these formulae and all the preceding ones. Really don't understand why these "hidden" formulae are in the summary sheet to begin with and not in a separate sheet that collects and summarises all charting data in a structured manner.
I hope you find this helpful.
bwk4s Hello Brandon
Lets take a look on differents between Assets and Debts. In Assets you've got additional column to group your assets. Only the groups are visible on summary sheet.
To understand what's going on here you can use the trace command.
select Cell L13. It refers to cell V6, it has a formula but not the value. We can change it. change the display format from ";;;" to general (for example). repeat it for whole range Q:Y (it has everything what we need)
Now you can see the formulas and how it works.
If you want to do the same with debts, you have to add category to your Debts table and do similar things to assets (write down all categories use match and rank function to change the order based on value, change reference from TBL_Assets to TBL_Debts). Anything else in my opinion like changing source of chart will be easy to you .
Hope this helps you.
Regards
Wojciech Siwiec