@jpfieber: I’m just now starting to dive into the Budget Plan and figure out how it works. I was hoping to marry the functionality of this sheet and my Recurring Expense Entry/Generated Expenses sheets. They’re ultimately holding the same data, and they’re surprisingly similar under the hood! It turns out that I use a lot of similar functions in my Generated Expenses
sheet.
One of the similarities I find amusing is our conclusion to use the REPT()
function iterated over an occurrence row (cell AF3
on your Budget Plan
; cell M7
on my Generated Expenses - Upcoming
sheet). Working that out was tedious, but ended up being the key to bust my solution wide open! Then going through your solution just now, I noticed you already did it. I reinvented the wheel for nothing! Lol. But I did notice that the formula I came up with was different than yours.
Your formula:
={"Description"; ARRAYFORMULA(
TRIM(
TRANSPOSE(
SPLIT(
QUERY(
IFERROR(REPT(
OFFSET(B4,0,0,COUNTA(B4:B),1)&"♥",
J4:J
)),
,
999^99
),
"♥"
)
)
)
)}
I also noticed that your formula spits out an extra row that’s being covered up by an IFERROR check. This also makes the “Occurrences” column show a 1 after the last occurrence row.
My formula does not do this, If you would like to give it a try and use it in your template? I replaced it in my local copy and everything looks great!
My formula:
={"Description";INDEX(
SPLIT(
FLATTEN(
SPLIT(
JOIN(
""
,INDEX(
REPT(
BYROW(
$B$4:INDEX($B$4:$B,MATCH("zzz",$B$4:$B))
,LAMBDA(x,TEXTJOIN("|",0,x))
)&"@"
,$J$4:INDEX($J$4:$J,MATCH(9^9,$J$4:$J))
)
)
)
,"@"
)
)
,"|"
)
)}