Great, glad that solved it. I need to update the sheet to deal with the empty arrays that result when there aren’t categorized transactions. In the meantime, I added a note to the instructions. Thanks again for your help!
I also saw your request to incorporate category groups. Sounds like a good feature. I’ll plan to add it, but I can’t promise when.
Hi Rob,
Great review sheet - have it working in one sheet but nothing but error messages in a second sheet
Have categorized transaction for both income and expense items. Cell T3 error is an array literal was missing.
| Use checkboxes to the right to show transactions for the categories and months selected | Budget Review | Year → | Last | 4 Quarters | Quarter → | Current | Quarter | Hide | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| by @rhowell version 0.94 | #N/A | #N/A | ||||||||||||||||
| #N/A | Category | Actual/Budget | Actual | Budget | Variance | Actual/Budget | Actual | Budget | Variance | #VALUE! | ||||||||
| #N/A | FALSE | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | |||||||||
| FALSE | ||||||||||||||||||
| FALSE | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | ||||||||||
| FALSE | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | ||||||||||
| FALSE | ||||||||||||||||||
| FALSE | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | ||||||||||
| FALSE | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! |
Any idea where I should look to get it to work in the second sheet?
Ron
Hi @rgsidor,
Have you tried installing a fresh copy of the sheet?
If that doesn’t work, a few other things to try: Does your Categories sheet have any categories that aren’t assigned to income/expense/transfer? Does your Categories sheet have column headings for for all the months this year?
Hi Rob,
Thanks for your response. I reconfirmed each of your suggestions.
I deleted and reinstalled your sheet, verified that all Categories are either income, expense or transfer, and and I have all 12 month column headings for calendar 2026.
Ron
Also noted that the #value error cells contains the following:
CHOOSEROWS parameter 2 expects number values, but got an error
Hi Rob, I just installed this by copying to a test sheet, nothing populates and am getting 2 errors.
In T3:
“In ARRAY_LITERAL an Array Literal was missing values for one or more rows.”
In J2, O2, :
#N/A “No matches are found in FILTER evaluation.”
Things I’ve checked:
Reduced Transactions to 132 rows of data, all are 2026, every row is assigned a Category. All columns Date, Description, Category, Amount, Account, Account #, Institution populated. Columns to the right have some cells empty.
The Categories sheet has 34 data rows, each category has a Group, Type, and none are marked “hide from reports” and all months Jan 2026 through Dec 2026 columns are populated with at least a “0” (zero) or some number that is referenced from column E, “Jan 2026” (F-P have something like relative reference =e2)
Suggestions?
Hmmm. Would you please unhide the columns on the right side of the sheet and let me know what you see there?
I don’t know if you can see these screen clips well enough. And thank you for being so responsive!
Transactions sheet doesn’t appear to have any hidden columns to the right. Data is just blacked out for this screen clip.
Categories sheet doesn’t appear to have any hidden columns to the right
Copy of Budget Review tab below. Cell T3 will show that error when I hover over it.
Hi Rob,
Very bizarre. The sheet is now populated. No idea why or what changed.
Again, thanks for sharing your work. The sheet is very useful.
Regards,
Ron
1 Like
@ghsubs1 If you’re still having trouble, please replace the formula in cell T3 with the following and let me know if it shows an error message:
=let(
select1_,"Income", select2_,"Expense", subtotal_label_,AR9, total_label_,AR10, null_label_,AR11,
m_q_,V1,
date_format_,"mmm yyyy",
categories_months_,AV4:AV,
current_,if(V1="Month",today(),if(mod(month(today()),3)=0,today(),eomonth(today(),3-mod(month(today()),3)))),
start_,if(V1="Month",eomonth(current_,-columns(U3:AG3)),eomonth(current_,-columns(U3:AG3)*3)),
categories_quarters_,query(transpose(categories_months_),"select quarter(Col1), year(Col1)",0),
query_by_col_name_,
LAMBDA(data_, query_, headers_,
QUERY(
{data_},
LET(data_row1_, ARRAY_CONSTRAIN(data_, 1, COLUMNS(data_)),
REDUCE(query_, FILTER(data_row1_, ISTEXT(data_row1_)),
LAMBDA(query_result_, data_col_name_,
REGEXREPLACE(query_result_, "(?i)`" & REGEXREPLACE(data_col_name_, "([^a-zA-Z0-9 ])","\\"& "$1") & "`", "Col" & MATCH(data_col_name_, data_row1_, 0))))),
headers_)),
hidden_,iferror(query_by_col_name_(Categories!A:ZZ,"select `Category` where `Hide From Reports` = 'Hide' label `Category` '' ",1),""),
selected_,lambda(select_,query_by_col_name_(Categories!A:ZZ,"select `Category` where `Type` = '"&select_&"' label `Category` '' ",1)),
query_data_,
iferror(query_by_col_name_(Transactions!A:ZZ,
"select `Category`, `Amount`, `Month`, year(`Month`), quarter(`Month`)
where `Month` >= date '"&TEXT(start_,"yyyy-MM-dd")&"'
"&ifna(join(" ",arrayformula(" and `Category` <> """&hidden_&""" ")),""),1),"QUERY_DATA_ ERROR"),
pivot_,iferror(transpose(query(query_data_,if(m_q_="Quarter",
"select Col4, Col5, sum(Col2) group by Col4, Col5 pivot Col1 order by Col4 desc, Col5 desc label Col4 '', Col5 '' ",
"select Col3, sum(Col2) group by Col3 pivot Col1 order by Col3 desc label Col3 '' format Col3 '"&date_format_&"' "),1)),"PIVOT_ ERROR"),
head_,iferror(if(m_q_="Quarter",arrayformula(CHOOSEROWS(pivot_,1)&" Q"&CHOOSEROWS(pivot_,2)),CHOOSEROWS(pivot_,1)),BYCOL(pivot_,LAMBDA(col_,"HEAD_ ERROR"))),
separator_,BYCOL(pivot_,LAMBDA(col_,IFERROR(1/0))),
data_,iferror(CHOOSEROWS(pivot_,sequence(rows(pivot_)-if(m_q_="Quarter",2,1),1,if(m_q_="Quarter",3,2),1)),BYCOL(pivot_,LAMBDA(col_,"DATA_ ERROR"))),
selected_pivot_,lambda(select_,
iferror(query(data_,"select * where "&join(" or ",arrayformula("Col1 = """&selected_(select_)&""" ")),0),BYCOL(pivot_,LAMBDA(col_,"SELECTED_PIVOT_ ERROR: "&select_)))),
subhead_,lambda(select_,
iferror(BYCOL(selected_pivot_(select_),LAMBDA(col_,if(col_=CHOOSECOLS(selected_pivot_(select_),1),UPPER(select_),""))),BYCOL(pivot_,LAMBDA(col_,"SUBHEAD_ ERROR: "&select_)))),
subtotal_,lambda(select_,
iferror(BYCOL(selected_pivot_(select_),LAMBDA(col_,if(col_=CHOOSECOLS(selected_pivot_(select_),1),UPPER(subtotal_label_&select_),sum(col_)))),
BYCOL(pivot_,LAMBDA(col_,"SUBTOTAL_ ERROR: "&select_)))),
subtable_,lambda(select_,iferror({subtotal_(select_);selected_pivot_(select_)},BYCOL(pivot_,LAMBDA(col_,"SUBTABLE_ ERROR: "&select_)))),
subtotals_,iferror({subtotal_("Income");subtotal_("Expense")},BYCOL(pivot_,LAMBDA(col_,"SUBTOTALS_ ERROR: "&select_))),
total_,iferror(BYCOL(subtotals_,LAMBDA(col_,if(col_=CHOOSECOLS(subtotals_,1),UPPER(total_label_),sum(col_)))),BYCOL(pivot_,LAMBDA(col_,"TOTAL_ ERROR"))),
null_pivot_,selected_pivot_(""),
result_,
if(query_data_="QUERY_DATA_ ERROR",{query_data_,query_data_},
if(pivot_="PIVOT_ ERROR",{pivot_,pivot_},
{if(CHOOSECOLS(null_pivot_,1)="SELECTED_PIVOT_ ERROR: ",
{head_},
{head_;{null_label_,choosecols(null_pivot_,sequence(COLUMNS(null_pivot_)-1,1,2,1))};separator_});
total_;separator_;subtable_(select1_);separator_;subtable_(select2_)})),
result_)
Glad it’s working! There was probably some data missing that the sheet expects to be there. If you have trouble again, try the formula I suggest above and report back.