Transaction Sheet is Not Offering Statement Name Created on Statement Sheet

Other user has noted: “I’ve reloaded and started over several times and still am encountering the same issue. I’ll process a Statement, then go to the Transactions Tab, but the statement name never appears in the Drop-Down choices. Just says Loading…” Heather offered suggestion to delete the statements column from the transactions sheet and reload.

I have this same issue on one of my tiller sheets (I have two - my main account one, and an alternate one with other accounts). My personal one works great and I’ve been using it for some time. The other I recently decided to install the statement option and start using it… no luck. I put the first info into the statement sheet and nothing comes through on the transaction sheet statement column…so it shows no statement titles at all available to use in the transactions sheet. I’ve reset as you suggested (removed and restored) and still nothing is available on the transactions sheet. What do I try now?

@brianandveronicac I found these tips from a different thread.

Wondering if you have tried these steps. * Confirm you’re expecting the Accounts to show up in D3:D , not A3:B , right? The labels are a little confusing, but columns A & B serve the purpose of creating a unique identifier for the statement; they are not for selecting the account name.

  • Unhide columns M:V by clicking on the hide icon in the column L header. Confirm that you see your accounts listed in P3:R .
  • Confirm that the Accounts sheet range references are working in V3:V7 .

If all of those checks look ok, the issue is likely in your Accounts sheet. To check that sheet, unhide columns E:T by clicking on the hide icon in the column D header. In the range F2:Q , you should see all of your accounts listed.

Hi All

I too have been facing this problem ie no issues with Statement / S.Details however no available dropdown in the Statements column in the transaction sheet. No effect with reload and troubleshooting tips @warren all seem to be appropriate. Doing some digging around - Data validation of the Statement column from the Transaction sheet appears to refer to column N3:N on the Statement sheet which is blank for me; should this be O3:O? I’ve changed it to this and it seems to work, but as I am not a spreadsheet whiz kid it would be good to know that this is the right move…

:wave: @Quarth ! Thanks for pointing this out. You might be on to something.

@jono can you double check on this and see if we need to make an update to Statement and Statement details or the data validation range that’s being used in Transactions when that’s added?

Thanks so much @warren, I will go over these carefully, but at first glance they look intact.

@Quarth - wow! This is exactly it! The columns on the statement sheet all seem to have shifted over and it was referencing the blank column. Magic! it now references perfectly! @heather it will be good to know if this is needs an update - thanks for the follow-up. Warren, Quarth and Heather, sure appreciate all your help! That was a head scratcher! Happy Friday!

I just took a look at this. I started a fresh Foundation template and added the Statement and Statement Details sheets.

I can confirm that on the Transactions sheet, the Data validation for the added Statement column is looking at the range in Statements!$N$3:$N. But the N column in the Statement sheet is empty.

It appears that an additional Column A was added with a Tiller Community logo, which shifted all the column references over, causing the problem.

A temporary fix is to change the Data validation formula on the Transactions sheet Statements Column to Statements!$O$3:$O.

I will discuss with @randy and @heather what the best long term solution is off-line.

Thanks for solving this @Quarth.

Jon

@jon @heather @brianandveronicac glad to help

Sorry about the hassle everyone. I had added a column A for the Tiller Community badge and forgot about the reference dependency.

I have updated the Statements solution to version 1.01 with a change that removes a spacer column to preserve the new badge column without moving the referenced data in column N.

This should work great for new users installing the sheet. Unfortunately, those of you that installed in the interim may need to update the data validation manually as @jono lays out above.

If your Statements sheet is working, I’d recommend against upgrading (to version 1.01) since you will need to port all the manually-entered data into the new sheet.

1 Like

@randy thanks so much, this worked great for my new sheets that I am just beginning on. Appreciate the update! Cheers!

1 Like

I’m not sure if I caught everything in this thread, but I upgraded and the “Last Balance” field on the Statement Details sheet is N/A,
=QUERY(Statements!A:L,"SELECT C,H WHERE D='"&B2&"' ORDER BY C DESC LIMIT 1",0) and when I view Unreconciled Transactions, it just says False where it should have the last balance.

=QUERY(Statements!B:M,"SELECT D,I WHERE E='"&B2&"' ORDER BY D DESC LIMIT 1",0)

Also, just a suggestion for logo placement, I think the details should be moved down a row or two and put the logo there, rather than shifting everything to the right.

Good catch, @yossiea, and thanks for your diligence in proposing a fix. I just published an updated version.

(To be honest though, I can’t see where that lookup is used in the main body of the sheet. Do you just leave the hidden area unhidden and reference it?)

I agree with you that the logo placement on the Statements sheet is clumsy. I do try to keep simple lookup data references to simple, functional headers when possible, so that is why I put it on the side. It’s kind of a compromise amongst dueling conventions.

It’s used, at least on my sheet, when I view unreconciled transactions. It’s used to get the latest balance and that shows up on the header section. I keep the formula columns hidden but expanded it to investigate.