Manually Added transactions - #REF error on type column

Got it, @jpfieber and @mlux:

Apologies for my fumbling around here. When the Type column is renamed, “Category Type,” the formula works for transactions added by the Labs Add-on.

Evidently, a column named, “Type” is scripted in the Add-on to return the Account Type, not the Category Type you’re seeking.

Try this edit to @jpfieber’s formula, replacing G with the column letter for your Transactions Sheet’s Category column:

=ARRAYFORMULA(IF($G:$G = “Category”, “Category Type”, IFERROR(VLOOKUP($G:$G,Categories!$A$2:$C,3, TRUE),"")))

BTW, if you remove the dollar signs from the formula, this will make the column references relative so that if you add columns to the sheets to which the formula looks, it will automatically update, too. As it stands, it is referencing absolute column positions, regardless of their content, and that will produce unexpected things when columns are added or subtracted.

Noting this because my memory isn’t good enough to keep all of those changes straight when I edit a sheet!

1 Like