Balance History Tracker

[rachellii24]

Hi! Using this template as a building block to a vision I have. In doing that, I ran across an issue where when sorting an account, the sorting only took the date into account, & not the time. I changed the formula in A13 to this to fix it so it sorts in chronological order including the time.

=filter(sort(iferror(sort(filter(sortn(sort(filter(H13:P,(H13:H>=L2),(I13:I<=L3),(if(isblank(D6),H13:H>1/1/1900,K13:K=D6)),(if(isblank(D5),H13:H>1/1/1900,L13:L=D5)),(if(isblank(B5),H13:H>1/1/1900,O13:O=B5)),(if(isblank(B6),H13:H>1/1/1900,N13:N=B6))),1,false),9^9,2,1,true,3,true,4,true),{1,1,0,1,1,0,1,1,1}),1,true,3,true,2,true),"Nothing returned based on filter conditions."),1,if(F5="Descending",FALSE,TRUE)),{0,1,1,1,1,1,1})

[yossiea]

Thanks for that! I updated the template to version 1.2. @randy can you please publish to the Solutions and give credit?

Thanks!

[nspreitzer]

I get this error when I try to install. Any suggestions?

[randy]

I saw your message and just tried it, @nspreitzer. The install worked for me.

Were you able to resolve this? Are you able to access the master here? Is there anything unusual about your environment? Corporate Google ccount?

[nspreitzer]

Whoops! I forgot to respond. Sorry, @randy.

• I was not able to resolve, no. I tried 3 times and gave up.
• I am indeed able to access the master sheet you linked.
• Nothing unusual about my account that I can think of. Personal account, not corporate, not G-Suite.

[gcyr91]

how did you add that chart ?

[RonH]

Trying out this BH Tracker - thanks! The Sort by Date feature appears to look at the month and day, but not the year. Copy of the Descending Date inserted here …,

Consequently, 2024 Balances don’t appear until you scroll down to June, where a descending filter should put 2024 on top of list. …

[ramerkw]

To follow on with the date, the time frame seems to be ignored and the sort sorts the dates in lexicographic order. I removed then reinstalled the sheet before updating this thread.

This month ?!??

Ascending sort

Custom date range:

[yossiea]

I’ll have to take a look at it but as you say something seems odd.

[kyle.sullivan.me]

FYI, I’m seeing the same sorting behavior as @ramerkw

[ryan.lehms]

I’m seeing the same sorting behavior as well. I really wish I could get a solid graph of my balance history over time.

[ryan.lehms]

For what it’s worth, I’ve gotten a lot closer to the desired results by forcing the DATEVALUE() within the formula in cell A13.

=filter(sort(iferror(sort(filter(SORTN(sort(filter(H13:P,(DATEVALUE(H13:H)>=L2),(DATEVALUE(I13:I)<=L3),(if(isblank(D6),DATEVALUE(H13:H)>1/1/1900,K13:K=D6)),(if(isblank(D5),DATEVALUE(H13:H)>1/1/1900,L13:L=D5)),(if(isblank(B5),DATEVALUE(H13:H)>1/1/1900,O13:O=B5)),(if(isblank(B6),DATEVALUE(H13:H)>1/1/1900,N13:N=B6))),1,false),9^9,2,1,true,3,true,4,true),{1,1,0,1,1,0,1,1,1}),1,true,3,true,2,true),“Nothing returned based on filter conditions.”),1,if(F5=“Descending”,FALSE,TRUE)),{0,1,1,1,1,1,1})

[bryce.mcgrew]

@ryan.lehms can you elaborate? I’d love to get this to work. Also, are you creating the graph, or is it supposed to be built into this template (I can create easily enough of course, but wonder if my template is missing something).

[ramerkw]

Warning: copy/paste of the formula will give #ERROR because of the typographic quotes around the string literals. Just edit the formula to put in ordinary quote marks.

Also: The sort seems ok within the year, but multiple year range still unexpected result.

[Mark.S]

Use the Preformatted text forum message option when sharing formulas and then they can be copy/pasted without issue

[ryan.lehms]

Yeah, it gets us closer to the desired result, but the multiple year range is still borked. I ultimately gave up and switched the graph into scatter mode and called it a day.