Docs: Net Worth Tracker (Excel)

Randy, thanks for the sample of what the code should look like. I can confirm that yes, I am seeing everything except for the balances beginning in AG7.

I compared your sample, above, with what is in my file. I see “_xlfn.” as a prefix before CHOOSECOLUMNS. I’ve pasted my formula from AG7, below.

=IF(OR(INDEX($AA$7#,,1)="",AG2="",EOMONTH(TODAY(),0)+1<=AG$2),"",LET(tablesortedbydate,_xlfn.CHOOSECOLS(SORT(FILTER(BalanceHistory[[Date]:[Account ID]],BalanceHistory[Date]<=EOMONTH(AG2,0),""),1,-1),COLUMN(BalanceHistory[Date]),COLUMN(BalanceHistory[Account ID]),COLUMN(BalanceHistory[Balance])),IFERROR(INDEX(tablesortedbydate,MATCH(INDEX($AA$7#,,1),INDEX(tablesortedbydate,,2),0),3),"")))

Interesting. Looks like you implemented the install instructions properly.

I’m guessing the _xlfn. has to do with the versioning of their available functions. CHOOSECOLS() was added very recently.

I’ve had success with the instructions on a local workbook but haven’t tried them with OneDrive. It appears that is the root of the incompatibility. Is running the workbook locally an option?

Can you tell if your workbook has any external links? Everything should be self contained.

I’d prefer to have it in onedrive for the version control and auto-save. I’ll try moving it to a local folder first then, if that works, I’ll try moving it ot the local OneDrive folder.

Thanks for the quick reply. I’ll keep you posted.

Thanks. Sorry for the hassle. I am still learning about the best ways to share Excel templates. (It is a little easier in the Google ecosystem.)

I followed the directions and also had issues with formulas still being linked to the original spreadsheet. This happened on both the Net Worth and Budget plan templates for Excel. What I have found to work pretty well for removing all the links that don’t get cleaned up properly is this:

  1. Go into Edit Links and note part of the path of the remote file.
  2. Do a search across the entire workbook for a portion of that file path that you know will be unique to just that path.
  3. Go the first cell you find and copy the full path in the formula, including the apostrophes that surround it as well as the ‘!’ that follows.
  4. Start another Find/Replace (Ctrl H) and paste that path into the Find box. Leave the Replace box empty and click Find All.
  5. When you are confident that the cells found are the correct ones, click Replace… or Replace All, depending how confident you are!

Now when you go to Edit Links, you should find the Edit Links option disabled. If that’s true, you’ve succeeded. If not, repeat the steps until the Edit Links option disables, which means no external links exist in the workbook.

I have found this to be a pretty foolproof method for removing any remaining remote references. I hope this helps someone else.

I saved a copy of my master file on my desktop, deleted the existing net worth sheet and did a clean install. Didn’t fix the problem, unfortunately.

Hi All,
I am having the same issue. My account names are showing on the Net Worth Tracker, but the $ data is not showing up - it is blank. Has anyone found a solution to this?

Thanks,
Roland

It does have a file path in the formula pointing to OneDrive path. Actually looks like this path is in there 4 times…I can move it locally if you want me to try that. Perhaps it might work :thinking: even though @CatPhish .Want me to try? I will say I downloaded the Category Tracker worksheet and it works flawlessly. I was not successful in downloading the Insights worksheet. Similar situation, no data. :grimacing:

Same Here… I did exactly as @CatPhish with same “no joy” results.

Any more ideas/hypothesis?

A user shared a demo sheet that helped me get to the bottom of this issue…

The problem is that the Net Worth template doesn’t handle the situation well where the Balance column is to the right of the Account ID column in the Balance History sheet. (The Balance column precedes the Account ID in my development sheet.)

I’m thinking on the best way to fix this. :thinking:
Really sorry, everyone, for the delay and frustration. Grateful for your patience.

More soon.

Just posted a fix for the Balance History column order bug. If the sheet is not filling for you, please give version 0.94 (link in instructions) a try.

Let me know if it works for you!
(If it resolves the problem, I will probably clear out the posts about this issue.)

Hey @randy, so I downloaded v0.94, moved the worksheet and change my data links source and…nada. Still blank.

I then deleted the worksheet and moved the workbook out of OneDrive. I opened my downloaded v0.94 again, moved the worksheet to the workbook (now stored locally) and updated my data links…and I now have my balance data showing. It’s odd to me that the Category Tracker worksheet is (and was) working when the workbook was in OneDrive. I’m kind of wondering if I can move it back and it still work?

Either way, it’s good to go on my end. I may go back to see if I can download the Insights worksheet as I had been unsuccessful. Wondering if for some reason it’s a similar problem (OneDrive vs. Local).

Thanks sir.

Thanks for the feedback, @jfhitchcock07. I’m sorry this is not going smoother.

I definitely saw an issue with the way the balance data range was being pulled if the Balance column was to the right of the Account Id column (I didn’t have this issue in my dev sheet). This is what I fixed in 0.94.

Let me know if the template continues to work if/when you move it back to your local drive.

Randy

P.S. Would you be willing to share the non-functional local version (pre moving to OneDrive) with our support account (support@tillerhq.com)? I’m curious which formulas are failing.