How to prevent the Path displaying of the reference of particular sheets in formual of a cell

[NimishK]

Hi
Any ideas How to prevent the Path displaying of the reference of particular sheets in formual of a cell

The following was the original when coding

With ws
  .Range("CP" & curRow).Formula=SUMIFS('Sheet7'!S:S,'Sheet7'!A:A,Sheet5!A:A,'Sheet7'!K:K,Sheet5!$CP$1)
End with

when the Worksheet of different file was copied to new folder but with new file name got the following links.

in worksheet Cell formula displayed the following

=SUMIFS([COLOR=#ff0000][B]'C:\Samples\[ABCD.xlsm][/B][/COLOR]Sheet7'!S:S,[B][COLOR=#ff0000]'C:\Samples\[ABCD.xlsm][/COLOR][/B]Sheet7'!A:A,[B][COLOR=#ff0000]'C:\Samples\[ABCD.xlsm][/COLOR][/B]Sheet5'!A:A,[COLOR=#ff0000][B]'C:\Samples\[ABCD.xlsm][/B][/COLOR]Sheet7'!K:K,[COLOR=#ff0000][B]'C:Samples\[ABCD.xlsm][/B][/COLOR]Sheet5'!$CP$1)

So wanted to avoid the the Path or the Link marked in Red Above

the reason to avoid displaying path for any chance the file is deleted form the original folder. and still the link remains which could generate Error.
So wanted below formula to be displayed in the Cell(s). when File or workbook copied to another folder

=SUMIFS('Sheet7'!S:S,'Sheet7'!A:A,Sheet5!A:A,'Sheet7'!K:K,Sheet5!$CP$1)

Thanks
NimishK

 

[NimishK]

Hello

Any chance of avoiding the path as mentioned above ?

 

[Joe4]

I don't know that is possible. If you remove the path & file, then it will look internally to the sheets within the same workbook.
If you want the formulas to reference other files, you need to have the path & file in there.
So it changes the very nature of the formulas if you remove the file/path.

Note that you may be able to store the file/path in a single cell, and have all the formulas reference that cell and build the reference using the INDIRECT formula.
The advantage to doing something like that is if the file is deleted, you can easily/quickly change the reference to another file by updating that single cell.
Note that the INDIRECT function only works if the other workbook is open (doesn't work on closed Workbooks).

See here for details:
https://exceljet.net/formula/dynamic-workbook-reference

If you need an INDIRECT-type function that works on closed workbook, take a look at this Add-In: https://download.cnet.com/Morefunc/3000-2077_4-10423159.html

 

[LlebKcir]

If I understand what you are saying, a new workbook was created via coping a worksheet into a new workbook. Yes that will happen, it is a known issue with Excel going back more than a decade.

The only 2 workarounds I have found are:

1. Copy the text from Formula Bar, not directly from the cell (do not Ctrl+C on the cell) highlight the text in the Formula Bar, right click, copy. In the new workbook, click inside Formula Bar, right click, Paste (might be able to ctrl+v at that point, depends on how Excel is feeling that day)

This is time consuming, but will prevent the path linking you are seeing.

2. Create either a new Excel workbook and set all cells in the worksheet to TEXT thus they do not have any ability to operate on formulas. Build all of the formulas you want, copy/paste those formulas into the Formula Bar of the new workbook. This can also be done with a text editor (notpad, wordpad, gedit, notepad++, etc...)

 

[NimishK]

Very sorry for late reply and Thank you so much guys for your suggestions.

I think what i will do is to first remove all the External Links When opening the Workbook and Then Fixed the Path in the sheet where it is referenced to a link . Have not tried but would like to try. Any comments

Sub BreakAllLinks()

    Dim arrStrLinks As Variant
   
    arrStrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    
    For i = 1 To UBound(arrStrLinks)
        ActiveWorkbook.BreakLink _
        Name:=arrStrLinks(i), _
        Type:=xlLinkTypeExcelLinks
    Next i
End Sub

NimishK