Column gone wrong

Google Sheets
1

Hi, all. I’ve just noticed that a column that I’ve had hidden in my transactions sheet now has a REF error. But I’ve had the column hidden for so long that I don’t remember what it’s supposed to be :grimacing:, and I’m not very good with Google Sheet formulas. Here’s the formula in the header (in Column G if that matters):

=arrayformula(if(row($F$1:$F)=1,“Group”,iferror(vlookup($F$1:$F,{indirect(“Categories!$”&SUBSTITUTE(ADDRESS(1,MATCH(“Category”,Categories!$1:$1,0),4),1,“”)&“$2:$”&SUBSTITUTE(ADDRESS(1,MATCH(“Category”,Categories!$1:$1,0),4),1,“”)),indirect(“Categories!$”&SUBSTITUTE(ADDRESS(1,MATCH(“Group”,Categories!$1:$1,0),4),1,“”)&“$2:$”&SUBSTITUTE(ADDRESS(1,MATCH(“Group”,Categories!$1:$1,0),4),1,“”))},2,FALSE),“”)))

The REF error I’m getting is: “Array result was not expanded because it would overwrite data in G91.”

Anybody have a minute to make sense of this for me? My sheet seems to be operating just fine (there’s probably a reason I decided to hide this column in the first place), so more curious than anything. Thanks.

1 Like
2

Hello! Go down to cell G91 on your Transactions sheet and see if there is anything entered there. If there is, remove it and check to see if the error is gone. If it’s still there, it will complain about another cell with a value.

The formula you posted fills all the cells below it dynamically. If something else is entered in a cell that this formula would otherwise provide a value for, it will throw this error because it cannot resolve without overwriting the other value, if that makes sense.

So nothing should be manually entered into column G. If any cells in column G are populated, highlight them and hit backspace. This should resolve the issue.

1 Like
3

Thanks. That solved it, but maybe you can teach me a little something if you have a minute: where it was getting caught throughout Column G were various entries that had a $0.00 amount. I fixed the problem by backspacing in each of the (empty) Column G rows that corresponded to the $0.00 amount. (The $0.00 amounts are entries from the Paycheck Deduction Transaction Generator for which I’m no longer having any deductions taken at this point in the calendar year.) Do $0.00 transaction amounts give Google Sheets/Tiller a headache? Thanks.

4

FYI, I had the same issue with no deductions in the last pay-period, I updated the template so it no longer includes $0 deductions in the list you copy to your Transactions sheet. You can update via the Community Solutions add-on. The problem you are having is due to the ARRAYFORMULA function in that column. That function wants total control of the column, and if it encounters any values, even a blank space or a zero, it can’t complete it’s mission so it displays an error. Hopefully for this specific issue, updating the paycheck deduction template won’t copy over transactions with zero values and hopefully won’t collide with that column in the future.

2 Likes
5

Thanks. I saw that update and was actually hoping to keep my 0.00 for the sake of completeness, but I’ll upgrade if it fixes his issue. Thanks for all of your work on that sheet and for answering these questions.

1 Like