I think I’ve stumbled upon a bug in the Monthly Budget and Yearly Budget sheets.
In the Monthly Budget sheet, cell V15, there is a query formula with a header issue that causes the top row’s transactions after the header to be missing in the budget report actuals.
This image shows Category Dental in cell V15, when it should just be Category. The Dental portion is really the first transaction’s category.
This issue occurs when the same transaction’s row last column cell is empty.* The Query function attempts to determine how many header rows there are, and in this case it uses two, instead of the one header row that is intended for the Transactions sheet. If the second transaction also has an empty last column cell*, then it would use three. These extra rows that are used as headers are now missing in the budget report Actuals data.
Original cell V15 formula:
=iferror(query(Transactions!A:AH,"SELECT "&K26&",SUM("&K27&") WHERE "&K26&" IS NOT NULL AND "&K25&" >= date '"&TEXT(K15,"yyyy-mm-dd")&"' AND "&K25&" < date '"&TEXT(eomonth(K15,0)+1,"yyyy-mm-dd")&"' GROUP BY "&K26&" LABEL SUM("&K27&") 'Actuals'"))
A fix - set the query formula third parameter to 1 (currently omitted optional parameter), which is the number of headers in the data, instead of having query “guess”.
QUERY(data, query, [headers])
headers - [optional]
The number of header rows at the top of ‘data’. If omitted or set to -1, the value is guessed based on the content of ‘data’.
Monthly Budget fixed cell V15 formula - insert ,1 so formula ends ,1))
=iferror(query(Transactions!A:AH,"SELECT "&K26&",SUM("&K27&") WHERE "&K26&" IS NOT NULL AND "&K25&" >= date '"&TEXT(K15,"yyyy-mm-dd")&"' AND "&K25&" < date '"&TEXT(eomonth(K15,0)+1,"yyyy-mm-dd")&"' GROUP BY "&K26&" LABEL SUM("&K27&") 'Actuals'",1))
The Yearly Budget has the same issue in cell AR6.
Yearly Budget fixed cell AR6 formula - insert ,1 so formula ends ,1))
=iferror(query(Transactions!A:AI,"SELECT "&AQ3&",SUM("&AQ4&") WHERE "&AQ3&" IS NOT NULL AND "&AQ2&" >= date '"&TEXT(E3,"yyyy-mm-dd")&"' AND "&AQ2&" < date '"&TEXT(eomonth(AL3,0)+1,"yyyy-mm-dd")&"' GROUP BY "&AQ3&" PIVOT MONTH("&AQ2&") LABEL "&AQ3&" 'Category'",1))
* There is more to it than just the last column cell being empty, that is just what appeared to be the case in my testing. In my case, if I add data that is a date or number, that affects query’s header guessing more than if I add text. As if query is looking for text headers with data having so many numbers/dates relative to number of columns in the data, and maybe relative to number empty cells. If I have 3 dates in 22 columns of data, query guesses 1 header row.