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:
- Go into Edit Links and note part of the path of the remote file.
- Do a search across the entire workbook for a portion of that file path that you know will be unique to just that path.
- 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.
- Start another Find/Replace (Ctrl H) and paste that path into the Find box. Leave the Replace box empty and click Find All.
- 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.
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?
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 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.
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.
Really sorry, everyone, for the delay and frustration. Grateful for your patience.
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 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.
P.S. Would you be willing to share the non-functional local version (pre moving to OneDrive) with our support account (firstname.lastname@example.org)? I’m curious which formulas are failing.
I had issues with OneDrive for this download and for the Category Tracker. When I moved both the foundation spreadsheet and the download to my local documents folder, THEN moved the new Tracker (Category & NetWorth) in the foundation spreadsheet, edited links, then resaved the foundation spreadsheet back to OneDrive. All works perfectly. The problem is OneDrive…just not playing nicely. Love being able to use native excel and OneDrive for version control and security. Keep the excel templates coming! This is getting really great usefulness for me. One I would love to see for excel is the Travel Budget Tracker. Thanks everyone!
Any chance this could be easily expanded to handle custom date ranges larger than 12 months? Otherwise looks great!
If you look in the hidden area of the sheet in
AG2:AR2, you can see where the periods are rendered by formula. The formulas below reference these cells and pull the last balance on the last day of the months in those cells (e.g. if the cell says March 2023, the balance closest to 3/31/23 will be pulled).
I’d recommend either manually entering the dates you want in these cells or writing a formula to do it programmatically.
I decided to unhide Net Worth 9.4 today and played with it today. I’m impressed with its cool worksheet with an input data field to allow me to select “This year”. It beautifully displays a whole year picture.
Randy, thank you for creating this awesome worksheet.
Awesome. Really appreciate the feedback, @david. Glad to hear it is working for you.