I have named 2 ranges (single cells boxed in red in the screenshot) in my Balances sheet, so that I can call them in another spreadsheet easily. However, today, the order of my two checking accounts was swapped. And then the balances caused a mild heart attack while looking at my other spreadsheet.
What determines the order of those balances? Is there a way to force it to not change? It has only happened once in the few months that I have been using Tiller, but I would prefer to keep it consistent.
I believe each area in the Assets and Liabilities is sorted by when the account was last refreshed, with the most current at the top. These lists are dynamic and will constantly change, so you won’t want to set a named range there. There are hidden columns (J-Y) that help feed the content you see. I believe those are sorted as well, so again, not a good candidate for a named range. I’m thinking you may need to use something like a vlookup or a query formula to look through the listing of accounts (probably the J-O list) and pull the balance for the one you’re interested in. This would allow you to deal with the fact that the order of the accounts is unpredictable.
Roger that. I guess it had luckily (unluckily?) remained the same for a while.
I am using another sheet to match those rows and pulling cell data into a named range there dynamically now. Thanks!
I’m having the same issue. Where you able to figure it out with the named range? Is that still working. I’m trying to figure out how you were able to make it work with the named range.
Hey @Adrian , I realize this is a year later, but I just was going through old replies, and it may still be useful for you or someone else in the future.
I am solving this by looking for the exact name in the Balances sheet. I am doing it in 3 cells, but you could probably make it more complicated and stuff it into a single cell if you want to.
Cell 1 (B3): I am looking for the balance of my “Key Smart Checking” account =IFERROR(MATCH("Key Smart Checking*",Balances!B1:B30,0),0)
Cell 2 (C3): Making a nice string to call later with INDIRECT =IF(B3<>0,"Balances!D"&B3,0)
Cell 3 (D3): Grab the actual balance using INDIRECT =IFERROR(INDIRECT(C3),0)
