Excel can't update some of the links

dstirling

When I open my excel spreadsheet, I'm getting an error that it can't update some of the links.



When I bring up the edit links box, I can see that the links are from a recently deleted folder when I downloaded the latest version. When I try to point the links back to the sync drive I keep my Portfolio files on, it keeps wanting the original download folder (which was in my downloads file).



I tried updating by selecting the spreadsheet file in the sync drive, as well as change source and open source, but each time I bring open it again I get the same error.

When I select check status, it tells me that it's looking for the file in the download folder.



I also tried breaking the link (using a duplicate copy) but that didn't work either.

Is there any trick to updating?

Thanks

VidasMatelis

I believe in most case you will encounter this error after copying data from one Excel workbook to another. When you copy data, you supposed to copy data only (http://www.portfolioslicer.com/docs/upgrade.html), as the regular copy will also copy formatting rules (or validation rules) and in some cases these formatting rules will include reference to old workbook and that is why you are getting this message.
You can try:
- In the "Edit links" interface choose "Break links" and see if that helps.
- Try to find that reference manually and remove it.

To find these references look into tables that you copied in "src*" worksheets. Most like this will be Transactions table (srcTrans worksheet), but could be Accounts, Symbols, etc. tables.
Select any cell in the table and then choose from Home menu: Conditional formatting->Manage Rules. You will have to then edit each rule and check formula - make sure it does not reference external workbook.
To check if these external links are in Validation rules, you would have to select column values (for example all values in srcTrans table, column Account) and then click Data->Data Validation->Data Validation.. and check "Source" area for references to external table.
Let me know if you were able to find these links or need more help.

dstirling

So in the srcSymbols tab, when I select on of the cells, the rules is:

AND(AND(COUNTIF('C:\Users\Dave\Downloads\PortfolioSlicerV2.4-XL16\[PortfolioSlicerV2.4-XL16.xlsx]src'!#REF!, E61)=0, LOWER(E61)<> "cash"), LOWER(E61)<>"index")

So what would go in here to replace it?

dstirling

I opened the original downloaded file and it looks like the rule should be:

=AND(AND(COUNTIF(src!$S$4:$S$97, E2)=0, LOWER(E2)<> "cash"), LOWER(E2)<>"index")

Correct?

VidasMatelis

The formula is correct and the approach is correct - copy the formula from the downloaded workbook.
When applying formula

dstirling

Worked. Thank you