Editing Tiller Labs report fields

Google Sheets
, ,
1

I’m new here and still trying to get my head around the reports and template. Happy to be redirected to read up on where I can find information!

I created the category roll-up report, and I see it has a Notes column. I was trying to figure out how I could add an “Account” column to that, to see which accounts the transactions came from. Any help is much appreciated!

Thanks!

2

Good question, @sandybholt78.
I took a quick stab at this… I added a column H to the report after it generated. I then added this formula to cell H5:

={"Account";arrayformula(iferror(vlookup(D6:D&E6:E,{Transactions!$A$2:$A&Transactions!$B$2:$B,Transactions!$G$2:$G},2,false)))}

Essentially it performs a VLOOKUP() on the merged value of Date (Transactions column A) and Description (Transactions column B) and then looks up the Account field (Transactions column G).

If your column order differs from mine (likely!) you may need to remap the columns a little to get this to work right.

If you want to take this to the next level, unhide the sheet called Report Template: Category Rollup and make this change there and the data will appear in all of your future category rollup reports. IMPORTANT NOTE: the block from column A to G in the hidden template is reserved for the report content. You must place new, derived columns outside of this area otherwise they will be overwritten when the report generates.

Good luck,
Randy

1 Like
3

Thanks! I did have to remap the columns, but I also had to include the amount with the date and description to get an accurate result on the account.

I was worried that since it was an array formula, I would have to do the CTRL+SHIFT+ENTER, but it seemed to work without it.

(I’m an Excel person - never used Google Sheets before, but I’m learning!)

2 Likes
4

@randy

I found this discussion today when I was looking for a way to customize the output of the Category Rollup or Budget Rollup report. I didn’t even know the Budget Rollup report existed until I watched the webinar from earlier this month. This is great because I was thinking of adding Account and/or Tags info as part of reviewing the detail.

I would like to customize the Budget Rollup report to exclude Hidden Categories like Category Rollup report works. Is there a way to access the hidden parts that go from the Report Template to the actual report when I press the Create Report button? I am not seeing any hidden columns to view all the behind the scenes magic.

Thanks for any direction that you can point me in . . .

5

Hey @martha.rudkin!

I announced the new Budget Rollup report on May 5th. We are still sorting through the best way to share things. Sorry if you missed it.

Unfortunately, the approach above for customizing the report template will not work for hiding rows. (The approach above only extends the auto-generated report body with ARRAYFORMULA() lookups around the margins— it can add but can’t remove data.)

I’d need to modify the report-generating code to do what you want. That said, it’s not a HUGE project so I’ll add it to my list.

Thanks!
Randy