The Rollover
section wasn’t an issue, @randy. At the time I had all categories set to roll over (I’ve since toned things down) so there were zeroes in every field that didn’t have an actual rollover, but I did a test and cleared out some of the zeroes and the script still ran successfully, so I believe the bug is only in the Rollover Mods
section.
I also made some enhancements to my copy of the migrator template to respect the Hide From Reports
and Track
columns that you might want to consider including.
On the Instructions
tab I added thee new entries in columns D
and E
below the other Categories column lookups.
Categories/Type, =iferror(char(64+match("Type",INDIRECT("'"&$B$12&"'!$1:$1"),0)))
Categories/Hide From Reports, =iferror(char(64+match("Hide From Reports",INDIRECT("'"&$B$12&"'!$1:$1"),0)))
Categories/Rollover To, =iferror(char(64+match("Rollover To",INDIRECT("'"&$B$12&"'!$1:$1"),0)))
On the Categories Migrator
tab, I added a Track
column in column E
and set D1
and E1
to the following formulas.
={"Hide From Reports";ARRAYFORMULA(IF(ISBLANK($A2:$A),IFERROR(1/0),IFERROR(VLOOKUP($A2:$A,{INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E5&"$2:$"&Instructions!E5&""),INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E7&"$2:$"&Instructions!E7&"")},2,false))))}
={ "Track"; ARRAYFORMULA( IF(ISBLANK($A2:$A), IFERROR(1/0), IFERROR( IF(ISTEXT(VLOOKUP($A2:$A, { INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E5&"$2:$"&Instructions!E5&""), INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E8&"$2:$"&Instructions!E8&"") }, 2, false )),"Savings",IFERROR(1/0)) ) ) ) }
I also modified the Category
formula in A1
to grab transfers after all of the budgeted categories.
={"Category";transpose(INDIRECT("'"&Instructions!B10&"'!F2:2"));QUERY(INDIRECT("'"&Instructions!B12&"'!A2:C"),"SELECT A WHERE C='Transfer'")}
These modifications set the Hide From Reports
column based on what is in Copy of Categories
, set the Track
column to Savings
whenever a rollover category is found, and append the transfer categories at the bottom.
Finally, I removed the Type
formula from C2
and added a new formula in C1
based on the Group
formula in B1
.
={"Type";ARRAYFORMULA(IF(ISBLANK($A2:$A),IFERROR(1/0),IFERROR(VLOOKUP($A2:$A,{INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E5&"$2:$"&Instructions!E5&""),INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E7&"$2:$"&Instructions!E7&"")},2,false))))}
With these modifications I can do a single copy/paste of the entire tab into the Savings Budget (Prototype) Categories tab. Before the modifications I needed to do 3 copy/paste operations and manually set Hide From Reports
and Savings
; one to grab A:D
, one to grab F:~
(due to the missing Track
column) and one to copy the transfer categories.
I hope this helps others get on board with the new template!