What do you mean by "check each link" ? Check them for what?

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

|Fewer Letters|More Letters| |-------|---------|---| |TEXTAFTER|Office 365+: Returns text that occurs after given character or string| |TEXTBEFORE|Office 365+: Returns text that occurs before a given character or string| |WEBSERVICE|Excel 2013+: Returns data from a web service.|

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

^(Beep-boop, I am a helper bot. Please do not verify me as a solution.)
^(3 acronyms in this thread; )^(the most compressed thread commented on today)^( has 15 acronyms.)
^([Thread #41308 for this sub, first seen 1st Mar 2025, 12:11]) ^[FAQ] ^([Full list]) ^[Contact] ^([Source code])

Power automate can be used. Or power query. You can find out the error for a broken link and see the output and return broken for a broken link or whatever you want for a link that works

/u/Lamps2020 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

I can usually clear all links in either Data > Edit Links or Formulas > Name Manager

The Name Manager ones are the ones that are usually impossible to find.

Do you have any charts in the file? That's usually where I find unbreakable links.

You might also have some hidden named ranges, you can use vba to list them all out.

Following code is from KuTools and will list all link references in a new sheet with the location. Perhaps it will help:

Sub ListLinks()
Dim xSheet As Worksheet
Dim xRg As Range
Dim xCell As Range
Dim xCount As Long
Dim xLinkArr() As String
On Error Resume Next
For Each xSheet In Worksheets
    Set xRg = xSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    If xRg Is Nothing Then GoTo LblNext
    For Each xCell In xRg
        If InStr(1, xCell.Formula, "[") > 0 Then
            xCount = xCount + 1
            ReDim Preserve xLinkArr(1 To 2, 1 To xCount)
            xLinkArr(1, xCount) = xCell.Address(, , , True)
            xLinkArr(2, xCount) = "'" & xCell.Formula
       End If
    Next
LblNext:
Next
If xCount > 0 Then
    Sheets.Add(Sheets(1)).Name = "Link Sheet"
    Range("A1").Resize(, 2).Value = Array("Location", "Reference")
    Range("A2").Resize(UBound(xLinkArr, 2), UBound(xLinkArr, 1)).Value = 
Application.Transpose(xLinkArr)
    Columns("A:B").AutoFit
Else
    MsgBox "No links were found within the active workbook.", 
vbInformation, "KuTools for Excel"
End If
End Sub

Use this: https://www.microsoft.com/en-us/p/xlstylestool/9wzdncrfjptg?activetab=pivot:overviewtab

Go to Data → Existing Connections. That should show you everything going on.

You can also disable the prompts.

https://docs.microsoft.com/en-us/office/troubleshoot/excel/control-startup-message

Update:

I opened up a version of the excel sheet found on another drive and found that those links are working in that workbook.

What is interesting is that the links within the workbook that do open up a stored PDF seem to be identical to the links within the workbook that only open the "cannot open the specified file" message.

I now have absolutely no idea why the links are broken... Will set this post to solved when someone tells me why this discrepancy between workbooks despite same addresses happens.

/u/lkvee Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

Try this script:

//@OnlyCurrentDoc

function getRange(functionName, rangeAddressString) {
    const RANGE_RE = '(?i:' + functionName + ')\\s*\\(\\s*(((\'.+?\'|[^\\s]+?)!)?(?:[A-Z][A-Z]?[0-9]*:(?:[A-Z][A-Z]?[0-9]*|[0-9]+)|[A-Z][A-Z]?[0-9]*|[0-9]+:[0-9]+))\\s*(?:\\)|,)';

    let re = new RegExp(RANGE_RE).exec(SpreadsheetApp.getActiveRange().getFormula());
    let rangeAddr = (re != null ? re[1] : rangeAddressString).toUpperCase();

    let range = rangeAddr.indexOf('!') == -1 ? SpreadsheetApp.getActiveSheet().getRange(rangeAddr) : SpreadsheetApp.getActiveSpreadsheet().getRange(rangeAddr);
    if( range == null )
        throw new Error('Argument must be a range.')

    return range;
}

function CHECK_URL(urlRange) {
    let range = getRange('CHECK_URL', urlRange);

    let rows = range.getNumRows();
    let cols = range.getNumColumns();

    if( rows != 1 && cols != 1 )
        throw new Error('Range must be either a single row or a single column (or a single cell).');

    let result = range.getValues().flat().map(url => {
        if( url === undefined || url === null || url === '' )
            return undefined;

        let response = UrlFetchApp.fetch(url);
        return response.getResponseCode();
    });

    return rows >= cols ? result : [result];
}

Then put this in D2 - or where ever you want the results to be:

=check_url(B2:B)

Feel free to build something around this if you find it useful.

Try using GOTO Special Formulas to find the formulas and see if any of them have external links.

/u/KJ6BWB - please read this comment in its entirety.

Once your problem is solved, please reply to the answer(s) saying Solution Verified to close the thread.

Please ensure you have read the rules -- particularly 1 and 2 -- in order to ensure your post is not removed.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

GoTo Special Formulas may help. But it could also be in something other than a cell. Data validation lists, etc.

The trickiest one of these that I've encountered ended up being in a conditional format. Check the conditional formatting for each sheet (and make sure to set the drop-down to the entire worksheet, not just the selection).

But unfortunately I haven't found a way to definitively get Excel to tell you where broken link issues are. :/

Ctrl+H Find [space](*BROKEN LINK*) Replace with [just select and Enter] Replace All

EDIT: Don't forget the leading space!

https://preview.redd.it/h0va4bzd5q8f1.jpeg?width=537&format=pjpg&auto=webp&s=55c7970dc88bfef4b0420877c913e56d816b5ae8

/u/Single-Doubt1794 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

>=TEXTBEFORE(A1," (*BROKEN LINK*)")

That will only return the text before what is in the quotes.

Go to find and type in .xls, check the advanced options so it's searching in formulas, and that it's searching the whole workbook, then use "find all"

If there's another workbook connected from an errant paste of a formula or something it'll find it.

Additionally going to file - external links, and then break all of the links if there are any to be broken

There might be a named range that's causing the problem

Check

Formulas > Defined Names > Name Manager

2. Maybe check

Data > Queries & Connections

For any connections to other sheets

here's the kicker.

sometimes it's a hidden name and there are macros to unhide them and delete them. i use the following.

Sub Show_All_Named_Ranges()

Row = 1

For Each n In ActiveWorkbook.Names

n.Visible = True

Next n

End Sub

sometimes, it's part of a graph or chart.

sometimes it's part of a conditional format formula.

Data->Edit Links

I usually search for [ in cell values when I'm looking for a stray link. Usually gets the ones that aren't defined.

If you're using two different files, you'd better use Power Query for it. It might clean both tables and export a results table with clean data even with the files closed.

However, if you really need a formula, you can run an XLOOKUP or a FILTER on one file to locate the records that appear on the other file, but you're likely to need both workbooks open for it to work seamlessly.

I think you need to first determine what defines a duplicate? Is it just 1 column? Several columns? Exact matches or approximate.

Sometimes data quality changes how we define a duplicate. If you do exact only matches, you might miss some similar records.

Matching and deduplication can seem easy at first, but the devil is in the details when it comes to data. Feel free to ask me any questions, happy to help if I can.

/u/J_sandy1 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

https://support.microsoft.com/en-us/office/how-to-compare-data-in-two-columns-to-find-duplicates-in-excel-fbeab47c-dd7a-4cf2-8aaf-50fc19d85dcc