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

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
684
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
Code:
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
Code:
=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
Code:
=SUMIFS('Sheet7'!S:S,'Sheet7'!A:A,Sheet5!A:A,'Sheet7'!K:K,Sheet5!$CP$1)
Thanks
NimishK
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
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...)
 
Upvote 0
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

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top