I successfully added Category Tracker For Microsoft Excel - Tiller to my excel TILLER FOUNDATION.
I went to “Data ribbon”, “Edit Links” and “Change Source." It returned error, “there’s a problem with this formula. Not trying to write a formula.”
Are you saying, @tjones4852, that the worksheet insertion went smoothly but you are stuck at breaking the links to the master template file? You can’t even open the “Edit Links” modal? Or you are seeing that error once you try to break the links? Any chance there is an indication at which formula or cell is the issue?
One option would be to change the source manually. Are you seeing a reference to an external file path in some cells (like maybe L3 in the hidden area)? If you are, you can copy part of the file path, click “Edit/Find…” then select the Look In: Formulas option to see what other cells refer to the external path. You can go ahead and remove that part of the path manually.
For example, when referencing in-workbook data, the formula in L3 should look like:
=INDEX(FILTER(Categories,(Categories[Hide From Reports]<>"Hide")*(Categories[Category]<>"")),,MATCH(L$2,Categories[#Headers],0))
Thanks for the video. Super helpful… though it looks like you are doing everything correctly.
Can you try the suggestion in the second paragraph of my earlier response? It looks like the formula in cell A13 is broken too (presumably due to the external reference issue). Can you send me that formula in this thread?
In cell A13 =SORTBY(L$3#,$N$3#,1,$M$3#,1,$L$3#,1)
That looks fine. Next step in the debug would be to check the data that formula is referencing by unhiding the hidden area to the right. If any columns contain #REF errors, let’s peek at those formulas.
I might add that the formulas do not work because the CHANGE SOURCE shown in the previous video would not accept the change to P:\014 FINANCIAL & TAX\00 Tiller\Tiller-Foundation.xlsx
I can’t speak to why the Change Source option isn’t working, but you can accomplish the same functionality manually by just doing a find and replace to wipe out that extraneous file path to the external workbook.
Your formula looks something like this:
=INDEX(FILTER('P:\014 FINANCIAL & TAX\00 Tiller\Tiller Community Sheets\Category Tracker\220722_CategoryTracker_v1.62.xIsx'!Categories[#Data],('P:\014 FINANCIAL & TAX\00 Tiller\Tiller Community Sheets\Category Tracker\220722_CategoryTracker_v1.62.xIsx'!Categories[Hide From Reports]<>"Hide")*('P:\014 FINANCIAL & TAX\00 Tiller\Tiller Community Sheets\Category Tracker\220722_CategoryTracker_v1.62.xIsx'!Categories[Category]<>"")),, MATCH(LS2,'P:\014 FINANCIAL & TAX\00 Tiller\Tiller Community Sheets\Category Tracker\220722_CategoryTracker_v1.62.xIsx'!Categories[#Headers],0))
References to the sheets and columns (e.g. Categories[#Data]) are preceded by references to external workbooks (e.g. 'P:\014 FINANCIAL & TAX\00 Tiller\Tiller Community Sheets\Category Tracker\220722_CategoryTracker_v1.62.xIsx').
The formula in cell L3 should look like:
=INDEX(FILTER(Categories[#Data],(Categories[Hide From Reports]<>"Hide")*(Categories[Category]<>"")),,MATCH(L$2,Categories[#Headers],0))
If you can manually find and replace the path string (make sure to search within formulas) with nothing (to blank it out), you should be able to redirect all instances of these external references to point to your local workbook.
Make sense?
When I do as directed and basically remove ‘P:\014 FINANCIAL & TAX\00 Tiller\Tiller Community Sheets\Category Tracker\220722_CategoryTracker_v1.62.xIsx’! i get the same error that I received originally.
At this point I am deleting the Category tracker to take up again in the future
Sorry to hear it still isn’t working, @tjones4852. Let me know if you want to try to pick this back up later.
Using MS 365, attempting to load again. Followed the instructions to add the sheet. 5. Leave the “Create a copy” checkbox unchecked. If I leave unchecked it returns this error.
Did the copy operation work ok? I think the issue is that the workflow is just pulling the last remaining worksheet out of the source workbook. You can probably ignore that part of the instructions.
I had to enable copy flag and it came into my foundation.
When I went to EDIT LINKS in order to attach to my foundation, this is the error I received.
Thanks for trying to share a video. Those can be helpful with debugging. Unfortunately, it came through to the community only as a still frame.
If the Edit Links workflow isn’t working, another option is to find the drive-path that Excel adds to the formulas to reference an external file and search your workbook/worksheet (including formulas) for that text. You can basically manually remove it in the handful of places it appears to return the formulas to referencing the local data.
I believe your email blocked the video so I had to send the screen shot.
So the proper path is “\QNAP3502\Personal\014 FINANCIAL & TAX\00 Tiller\Tiller-Foundation.xlsx”
When I do a search in the Tracker for 220722, i find the following. I tried to remove the “” around the proper link and same error.
Try doing a find and replace— including in formulas!— everywhere in the worksheet to:
- Replace this:
'\\QNAP3502\Personal\...Tracker_v1.62.xlsx'! - With nothing
Let me know if it works.
I did a find and replace for ‘\QNAP3502\Personal\014 FINANCIAL & TAX\00 Tiller\Tiller Community Sheets\Category Tracker (Would not load)\220722_CategoryTracker_v1.62.xlsx’! with blank and it returned the same error I originally
sent to you.
If I did as you suggest it would leave 220722_CategoryTracker_v1.62.xlsx’!Categories[#Data] which would not function properly anyway.
I should also mention that I did check regional and separator in system and excel as well. Bing Videos
It’s hard for me to see the full formulas from what is provided in this thread so I can’t tell you exactly what to replace but, essentially, if you see a reference in a formula that looks something like this:
'\\QNAP3502\Personal\...Tracker_v1.62.xlsx'!Categories[#Data]
It needs to be reduced to this (with no filename and path prefix):
Categories[#Data]
When you do the Edit Links workflow, essentially we are just trying to more-automatically do this find and replace operation to remove the external-file references from the cell references.
Make sense?