Hi, I’m new here. I’ve created a custom CATEGORY column called SEGMENT to sub-categorize within GROUPS (for personal, business1, business2, etc.). I figured I would keep GROUPS as the generic group of expenses, e.g. Materials & Supplies. How can I create a P&L based on only one of these SEGMENTS at a time?
Example:
Category | Group | Type | Segment
|Materials & Supplies | Materials & Supplies | Expense | Personal
|Materials & Supplies M | Materials & Supplies | Expense | Biz M
|Materials & Supplies R | Materials & Supplies | Expense | Biz R
|Materials & Supplies F | Materials & Supplies | Expense | Biz F
Thanks Blake,
What I’ve done is create an additional Category called SEGMENT with a dropdown for my 5 or so business categories: business1, business2, rental, personal, etc. So far so good.
What I need is add a dropdown to the lovely Tiller templates like I’ve done below, however I’m finding the QUERY formulas that they use are very complex (though I’m pretty savvy with Sheets) and the tables in say, the Quick Insights sheet, are not populating even after I’ve tried to modify the code. Notice the blank lines under the Top 10 lists:
Undo all the stuff you did above. I guess maybe you could leave it and then undo it later.
Create new tab called SubCategories, type SubCategory in cell A1 and type Category in cell B1. Type all the SubCategories you want to use in column A starting in cell A2.
Go to Transactions sheet and add column to the left of the group column and put SubCategory in row 1 of that new column. Let us say this new column is column H.
Now go to cell H2 of the Transactions sheet. Chose Data, Data Validation. Cell range needs to be Transactions!H2. Criteria should be “list from a range” and then =SubCategories!$A$2:$A$30 Check box called “show dropdown list in cell” and check circle called “show warning”. Then click “save” button.
Go to cell H2. Grab lower right corner off cell and drag down to bottom of the whole column. This will make everything you did in #4 apply to all the other cells in column H.
Now you are done. For every category you have, you will now have a dropdown box of subcategories in column H of the transactions sheet. Below are the categories and groups from my categories sheet. It tracks an apartment rental with four units. The SubCategories I use are Unit 1, Unit 2, Unit 3, Unit 4.
To run a P&L report, select hide in the “hide from reports” column of the categories sheet for every category except for the categories you want the include in your report.
Note the SH (Sky Hill) at the end of every category name. If you wanted to track another apartment rental, do everything the same but use something different than SH.
I know you are not tracking apartment rentals but this should give you enough to get you to where you want to be.
Please let me know whether this works for you or not.
Blake, your system is almost the same as mine, except instead of SubCategory I call them Segments. So I am already up to speed on categorizing transactions as you can see from my screenshots.
Manually selecting HIDE FROM REPORTS for every Tx is not an efficient way to run reports IMO. There’s no way I would do this manually for every Tx every time I want to run a report.
What is needed is either 1. to automate the ‘Hide from Reports’ column based on a selection somewhere else, or 2. add an additional dropdown to reports as I show in my last screenshot in blue, which selects only the Segment that we want included in the report.
I do hope Tiller considers adding this to the P&L generator and other templates as it greatly expands the functionality for those of us with multiple business segments.
Until then I will pursue solution #1, automating the ‘Hide’ column then running the report. Unfortunately this is not as advantageous, say for making multiple simultaneous reports of different business Segments.
Tiller please consider adding an additional Segment criteria to the reports!
Working from Blake’s idea of using the Categories>Hide from Reports column, I have “automated” the population of this column as I describe in solution #1 above.
On my SETUP tab, where I’ve listed my Segments and other things I’ve added to Tiller, I have a 2-col range named SEGMENTS :
On CATEGORIES tab, modify the Hide from Reports column with a formula and copy it down:\
Now, running the reports only includes non-hidden Segments/SubCategories.
Thanks for the different way of thinking about this, Blake.
I still would prefer an additional dropdown on the report page (or in the P&L generator’s options) to select a Segment right at the point when you are making the report, instead of having to go to my SETUP tab.
If the P&L had an option to choose a tag the way this category rollup report does, would that work?
Vs creating some one off “segment” option in the P&L or trying to build it so anyone’s custom column could be selected there (which is way harder) if the tag concept could be re-used that’s probably a bit more feasible.
