Manually Added transactions - #REF error on type column

Google Sheets
,
1

Apologies if this has been addressed somewhere already – I couldn’t come up with anything when I searched.

I’m having an issue where when I add a transaction manually (using the Labs add-on), it fills in the type of transaction in the Type column, which creates an error for the array formula that’s supposed to be filling that column.

Is there a way to keep the Labs add-on from writing in that column? Is there some other workaround I’m missing? I rarely look at the Type column but some of my other workflows refer to it, so I typically only notice when other things start acting up and I dig into it.

Thanks in advance!

2

I noticed the same thing the other day, definitely a ‘bug’ that needs fixing.

3

Hi @mlux:
I don’t use the Type column and don’t have it in my Transactions sheet, but when I added the column just now and then manually added a transaction, the column populated with the account type without an error.

Have you added a custom array formula to your Type column?

4

Hi Brad,

The Type column was added to my spreadsheet as part of the Business Dashboard. It’s possible I tweaked the array formula and forgot, but I think it was configured that way from the start, especially since @jpfieber is having the same issue.

I’ll tag this post so it’s clearer that it’s a clash between the Business Dashboard and Labs, not just a generalized bug. [edit: it doesn’t look like I can add tags to an existing post. Oh well.]

5

I don’t have the Business Dashboard, so there must be more than one ‘Solution’ that uses that column. My ‘Type’ column has the formula:
=ARRAYFORMULA(IF($C:$C = "Category", "Type", IFERROR(VLOOKUP($C:$C,Categories!$A$2:$C,3, TRUE),"")))

6

Hi @jpfieber and @mlux:

I hope to not confuse this further. @jpfieber, I added a column to my transaction sheet and then put your formula in the top cell. After I corrected the references to my Category column, (in my Transactions sheet, the Category column is G so I changed all of the Cs to Gs) it worked as expected.

This formula references the Categories sheet. Is it possible the columns on yours are not standard, as follows? (Most formulas will work dynamically if columns change. Some do not…just testing here.)

A=Category
B=Group
C=Type

I am not sure that helps, but sharing the experience in the event it does.

7

Just to be clear, @Brad.warren, when you say “it works as expected” do you mean you were able to manually add a transaction with the Labs add-on and it didn’t obstruct the array formula in the Type column?

8

My Categories sheet is standard, with the columns as you list. The formula works fine unless something is manually entered into it. If something is entered into it, as the “Manual Transaction” tool seems to do, then the column header gives a #REF! error, (Array result was not expanded because it would overwrite data in . So for clarification, I don’t think anything is wrong with the “Type” column, it’s arrayformula is working as it should. The issue is with the Manual Transaction tool. The tool should not be entering anything into that column, it should be allowing the arrayformula to look it up based on category, just like all the other transactions.

9

Good question and feedback, @mlux and @jpfieber. It works as expected when I add a line manually then type in the data. If I use the Labs Add-on, it, in fact, returns the error that you are seeing.

As you said, this appears to be a timing issue with the execution of the rules, where the data in the line for the Type column is being added by another rule. This keeps the array formula from populating downward. If you delete the data in the Type column after the Labs Add-on runs, the formula will populate downward as you want it to.

Agree with you that this is not the best solution.

10

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!
Best.

1 Like
12

I just changed the column from ‘Type’ to ‘Category Type’ in the formula, and added a manual task through the add-on, and it worked as expected. Nothing was inserted into the Category Type column, which was then automatically filled in by the formula, as it should be. Thanks!

1 Like
13

@randy can you validate this? I don’t think we’re using “Account Type” for anything but I do remember discussing some nuances of how Labs interacts with columns with arrayformulas in the Transactions sheet, but that discussion may have been in the context of the migrator, not the add transaction workflow.

14

Good catch on the fact that what’s being filled is the account type, not the transaction/category type. Thanks! I’ve made that change.

15

A post was split to a new topic: Adding a Category Type Column to the Transactions Sheet