Just occurred to me that this may be related to a bug identified above. See if either of the solutions mentioned in this post works for you:
Hi, @rhowell . Hoping you can help me troubleshoot. Iāve been using this sheet religiously and getting a lot of out of it. This morning, when I went in to check it, all of the transactions for the past several months that had been processed and appropriately struck through were all live again. That is, they no longer were struck through as if no corresponding transaction had been found for them. I havenāt changed a thing since when I checked it yesterday morning and everything was working as expected and checked it this morning and nothing was working as expected. Any suggestions? Thanks.
Hello: Iād first check that you still have the conditional formatting rule:
And that cell V1 has the following formula:
t=map(A:A,B:B,P:P,O:O,AH:AH,M:M,lambda(account_,date_,manual_reconcile_,override_,reconciled_id_,amt_,
if(row(account_)=1,"Reconciled",if(date_<>"",
if((manual_reconcile_)+((amt_=0)*(amt_<>"")*(date_<=filter(EB:EB,DZ:DZ=account_)))+(reconciled_id_<>""),TRUE),
iferror(1/0)))))
That should result in some TRUE values in column V.
1 Like
Thatās very strange. @brettanicus may be on to something regarding conditional formatting.
What happens if you manually reconcile a transaction by checking the box in column P? Does column V show TRUE and is the transaction struck through?
Also check that the dates of the corresponding transactions on your Transactions sheet are correct ā maybe a fill/Autocat went awry?
If the problem still isnāt apparent, what happens if you start with a fresh copy of the sheet and copy over your data from the green cells on the old copy?
Thanks, @rhowell and @brettanicus. The conditional formatting seems to be in place, and the formula in V1 is correct. BUT when I manually reconcile a transaction by checking the box in Column P, it does not show True in Column V, so it seems like thatās the problem. Any insight on whatās broken? I swear I didnāt change anything. At least I didnāt change anything intentionally. Itās very odd, and I appreciate the help.
Hi @rhowell. Sorry for the delay, just back from vacation.
For reference, here is the set up for a single credit card with a new copy of the Scheduled Transactions sheet. Columns B and G are set to the payment date and J is set to the closing date from my latest statement. The amounts in columns L Credit Card Pmt. Amount and M Transaction Amount both match the amount due from the statement. Notice the positive balance in Credit Card Current Activity Column K. This is not correct. The value should be -57.70 based on my transactions sheet.
I have tried both of the suggested linked solutions, and neither worked.
Option 1 results in a Credit Card Current Activity value in column K equal to the total charges going back to one month prior to the Credit Card Closing Date (so in this case it totals charges going back to 2/15, not 3/15).
Option 2 (after changing the formulas in M1 and K1) results in a correct Transaction Amount and Credit Card Pmt. Amount in columns L and M, but now Credit Card Current Activity is blank.
Any thoughts?
I will follow-up on my previous question about Closing dat invalid messages next!
Thanks for all your help Rob!
@rhowell I should add that in the first instance, the $976.52 under Credit Card Current Activity is actually the total of all transactions in then account from 2/15, the previous closing dat, including the payment made on the account which is why it is positive.
Itās been so long since I asked this that I donāt remember the exact details of the first question. I wanted to retrieve the amount from the transaction and autofill the āActual Amountā column with it since lots of mine need correcting.
I understand it doesnāt know the statement balance, what I meant was it seems to keep adding transactions to the balance due even after the closing date. The projections keep being too high. This seems to be the bug discovered below if Iām understanding correctly.
1 Like
Hi @rhowell I think I may have found the change thatās necessary. If you change line 6 of the formula in K1 from:
ifna(filter(ER:ER,EJ:EJ=descr_,EK:EK=closing_date_),iferror(1/0))),
to
ifna(filter(EP:EP,EJ:EJ=descr_,EK:EK=closing_date_),iferror(1/0))),
it seems to fix it with the activity since last statement lining up with the charges in transactions since the closing date specified in column J. Specifically the first argument of the filter function needs to be EP:EP not ER:ER.
I will have to see if it works in the next cycle, but am hoping thatās addressed the issue. Any thoughts?
Iām stumped. Did you try re-installing a fresh copy of the sheet and copying your data over?
Thanks for digging into this. I still havenāt had time for additional troubleshooting, but I suspect your fix will only work for part of the billing cycle. Let me know how it goes.
I never did figure it out, and whatever went wrong was seriously wrong. It started degrading the performance of other sheets because it seemed to be constantly running. Copying my data over to a new sheet didnāt work, so I wound up just starting from scratch. Not ideal, but it took less time to recreate things than I feared. Thanks for following up.
@rhowell So far for the May cycle of generated balances which were populated today, the credit cards seem to be pulling the correct balances forward for the future payments (the May payment is showing the charges accumulated since the April payment). I will keep you posted as the month progresses. Thanks again for all your help and for a great addition to the ecosystem. By far one of my favorite sheets.