Year-to-Date Comparison - Excel

Dude, you’re a spreadsheet machine. Great job.

1 Like

The Edit Links command is unavailable after copying the Year-to-date sheet so could not get it updated with the Tiller workbook.

I deliberately avoided creating any links/connections on this template, so you can skip the required step for changing them (as you’ve found, the ‘Edit Links’ button is greyed out since there’s nothing that can be edited). The formulas should find the data in your template as long as you have relatively normal Transactions and Categories sheets.

I expanded the grouping and changed the budget/categories starting dates and it works.
Thanks.

1 Like

Thanks for your work on this! Exactly what I was looking for.

1 Like

You continue to lead the charge to template parity, @jpfieber. So cool that you can turn a community request into a robust solution in just five days. Also really thoughtful of you to share credit with @jono, the creator of the Sheets version.

The Tiller team is excited to award you $300 as part of the Tiller Builder Rewards Program for building and documenting this template for the Tiller community.

:trophy:

2 Likes

@jpfieber I can’t figure out how to update this for my new 2023 Tiller workbook. The dates are not picking up 2023 and therefore no data. Any suggestions on where it’s broke? Should I download again and add to my new 2023 workbook?

Expand the + above column I and change cell K15 to 2023 and you should be set!

1 Like

Thanks - that worked perfectly. Glad I asked you because I would not have figured that out on my own.

1 Like

Just installed this add-on. Thank you for building it. On the top right corner, is it possible to add a filter to filter out specific categories. One of my categories is Savings contributions which is not exactly an expense but it is shows up in the ‘Expense Actuals’

1 Like

Suddenly the Year-to-Date Comparison stopped working. I am getting #ref! in Columns T U AB AE and no comparison to Budget. All the actual results are OK. What do you think happened?

I would suspect something got typed into a cell on that sheet and is preventing a formula from ‘spilling’ it’s data. I’d suggest trying to select the range O3:W40 and hit the delete key to delete anything in those cells. Do the same for the range Y3:AE04. If that doesn’t do it, I’d probably just start fresh with the template, since you won’t be losing much in the way of data. Delete the existing sheet and then recopy from the original download.

Joe,

The delete cells idea did not work so I deleted the worksheet and downloaded a new template. The same exact thing happens.

George

I just re-added it to my workbook and it’s working, so I don’t think the problem lies with the template itself, it must be something different about your workbook/data. Does your column W show a list of ranges? For example, mine shows:
E2:O2
E3:O3
E4:O4
E5:O5
E6:O6
E7:O7
E8:O8
E9:O9
E10:O10
E11:O11
E12:O12
E13:O13
E14:O14
E15:O15
E16:O16
E17:O17
Etc.
The sheet is getting these from your Categories sheet. If it can’t find these, that would break stuff.

Weird

Budget Range
Q2:\2
Q3:\3
Q4:\4
Q5:\5
Q6:\6
Q7:\7
Q8:\8
Q9:\9
Q10:\10
Q11:\11
Q12:\12
Q13:\13
Q14:\14
Q15:\15
Q16:\16
Q17:\17
Q18:\18
Q19:\19
Q20:\20
Q21:\21
Q22:\22
Q23:\23
Q24:\24
Q25:\25
Q26:\26
Q27:\27
Q28:\28
Q29:\29
Q30:\30
Q31:\31
Q32:\32
Q33:\33
Q34:\34
Q35:\35
Q36:\36
Q37:\37
Q38:\38
Q39:\39
Q40:\40
Q41:\41
Q42:\42
Q43:\43
Q44:\44
Q45:\45
Q46:\46
Q47:\47
Q48:\48
Q49:\49
Q50:\50
Q51:\51
Q52:\52
Q53:\53
Q54:\54
Q55:\55
Q56:\56
Q57:\57
Q58:\58
Q59:\59
Q60:\60
Q61:\61
Q62:\62
Q63:\63
Q64:\64
Q65:\65
Q66:\66
Q67:\67
Q68:\68
Q69:\69
Q70:\70
Q71:\71
Q72:\72
Q73:\73
Q74:\74
Q75:\75
Q76:\76
Q77:\77
Q78:\78
Q79:\79
Q80:\80
Q81:\81
Q82:\82
Q83:\83
Q84:\84
Q85:\85
Q86:\86
Q87:\87

Joe,

I get this in Col W. I have 2023 Col e - P and 2024 Col Q to AB. But why is there a \ instead of AB?

OK so in cell L17 there is an equation =CHAR(64+K17) and \ appears in that cell. When I type in AB instead of this equation it WORKS!.

L16 has the equation =CHAR(64+K16) and the letter Q appears in that cell. SO that’s working.

Ahh, good sleuthing. Apparently the CHAR(64+K16) method of converting a column number to a letter only works with one letter. Since yours expanded to two letters, we didn’t get what we’re looking for. To fix this, replace L16 with =SUBSTITUTE(ADDRESS(1, K16, 4), "1", "") and L17 with =SUBSTITUTE(ADDRESS(1, K17, 4), "1", ""). This formula will work with single or double letters. I’ll update the template so future users don’t get bothered by this (and bump the version number to 1.01), thanks for helping track it down!

Joseph,

Your fix worked! Also, i figured out why it worked until November 2023. In November the Categories column went from Z (October) to AA (November) so that’s why the original =char function worked for 10 months then blew up in November!

Cool!

Thanks,

George

1 Like

I LOVED this for my 2023 budget. Will it work for my 2024 budget?