deleting #REF! errors (many) on a worksheet? 1

[TomYC]

I have inherited a large file in which one of the worksheets is a summary of items from many other worksheets. A few of these detail worksheets have been deleted, thus the #REF! errors, that unfortunately appear in lengthy summations (example follows). I've got hundreds of 'em on this single worksheet, and would like to automate the process of removing all of them from the formulae.
Here is one of many, many examples:
='10611020 CTU Detail'!B4+'10611000 4 Monti Detail'!B4+'10611010 2 Cohen Tele Detail'!B4+'10611030 CCU Detail'!B4+'10611040 3 DSU Detail'!B4+'10611070 MICU Detail'!B4+'10611080 2 DSU Detail'!B4+'10611090 NSCU Detail'!B4+'10611100 SICU Detail'!B4+'10611110 4 Tower Detail'!B4+'10612000 3 MONTI Detail'!B4+'10612010 3 LIPPERT Detail'!B4+'10612100 NICU Detail'!B4+'10613000 4 DSU Detail'!B4+#REF!B4+'10613010 5 Monti Detail'!B4+#REF!B4+'10613330 4 Cohen Detail'!B4+'10614000 6 Monti Detail'!B4+'10615000 8 TOWER Detail'!B4+'10616000 2 Monti Detail'!B4+'10616010 BMTU Detail'!B4+'10616020 7 Monti Detail'!B4+'10616030 8 Monti Detail'!B4+'10616040 3 Cohen Detail'!B4+'10617000 3 TOWER PEDS Detail'!B4+'10617010 PICU Detail'!B4+'10619000 7 TOWER ORTH Detail'!B4+'10619010 6 TOWER Detail'!B4+'10616070 9 Monti Detail'!B4

 

[Gavona]

Not tested but try this:
Edit, Links check status and for those linked files that are not available then Break Links.

Now I would guess that your #ref!B4 in the formulae will become identical - #ref! (without the cell reference

So then use edit, replace to replace all occurences of +#ref! with nothing.

Gavin

 

[SkipVought]

Hi,

How many rows do you have?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Sorry, just re-read, I thought incorrectly that your problem was with references to other workbooks. Please disregard my post.

Gavin

 

[TomYC]

Thanks for the suggestion; yes, the cell references are to the same workbook, just other (departed) worksheets.
Skip, I've got about 150 rows, 25 cols or so, not every cell is as bad as my example (as some of them reference each other, no doubt), but enough that I would not look forward to editing each cell!

 

[SkipVought]

Before I did any mass correction, I'd STRONGLY advise redesigning your workbook structure or the process.

Gathering data from various sheets via formulas is very dicy. Not knowing your structure, I cannot venture a specific suggestion. But the primary way that I would gather data from a variety of sheets is via MS Query. It seems that you have a number of sheet with identical structure. There may be a way to get the data from ALL those sheets into ONE SHEET (table). Your world would be MUCH MUCH simpler as such!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

I totally agree; I have been revising this document for some time now, and have removed thousands of necessary keystrokes with various cell referencing techniques. This last summary sheet I hope to attack soon, but for purposes of quick display of YTD data I was hoping...

 

[Gavona]

Create a new worksheet and name it "Temp"
Edit Replace +#ref! with Temp!
Move Temp to a new Workbook
Delete that new workbook without saving
Edit,Links select the Link and break it.
All your errors appear as #Ref!

then use edit, replace to
replace all occurences of
+#ref! with nothing.
*#ref! with nothing
etc






Gavin

 

[TomYC]

Thank you Gavona--
It sounded very clever. The temp worksheet I filled with zeroes, then moved it to a new book (I'm up to Book3) and deleted without saving it. But I don't get the results you suggested. And if I break the link then all my values become 'flattened' or unconnected to any worksheet at all, and as this is a periodically updated file, that won't do.
At the moment I have cell formulae such as the following, which obviously works, since it gives no errors, but is inelegant.
='10611020 CTU Detail'!B4+'10611000 4 Monti Detail'!B4+'10611010 2 Cohen Tele Detail'!B4+'10611030 CCU Detail'!B4+'10611040 3 DSU Detail'!B4+'10611070 MICU Detail'!B4+'10611080 2 DSU Detail'!B4+'10611090 NSCU Detail'!B4+'10611100 SICU Detail'!B4+'10611110 4 Tower Detail'!B4+'10612000 3 MONTI Detail'!B4+'10612010 3 LIPPERT Detail'!B4+'10612100 NICU Detail'!B4+'10613000 4 DSU Detail'!B4+[Book3]TEMP!B4+'10613010 5 Monti Detail'!B4+[Book3]TEMP!B4+'10613330 4 Cohen Detail'!B4+'10614000 6 Monti Detail'!B4+'10615000 8 TOWER Detail'!B4+'10616000 2 Monti Detail'!B4+'10616010 BMTU Detail'!B4+'10616020 7 Monti Detail'!B4+'10616030 8 Monti Detail'!B4+'10616040 3 Cohen Detail'!B4+'10617000 3 TOWER PEDS Detail'!B4+'10617010 PICU Detail'!B4+'10619000 7 TOWER ORTH Detail'!B4+'10619010 6 TOWER Detail'!B4+'10616070 9 Monti Detail'!B4

 

[Gavona]

I did test.
What happens to the formula if you now break the links to Book3?


Gavin

 

[TomYC]

When I have tried that, the entire formula disappears, and the cell value that contains the formula becomes hard-coded. I can live with what I have, but we were so close!!

 

[Gavona]

OK I cannot succesfully replicate what I did yesterday. But I can offer a solution of sorts:

My test formula:
=B21+#REF!A1+Sheet1!A20

Edit, Replace
+#REF!??+
with
+

Gives: =B21+Sheet1!A20

You may also need to cope with absolute formulae:
Edit, Replace
+#REF!$??+
with
+

Edit, Replace
+#REF!$?$?+
with
+

Edit, Replace
+#REF!?$?+
with
+

and more if the linked cells are after row9 you will need to add in an extra wildcard ?
Similarly if they are after column Z
.....


So not so clean as the solution I thought I had found.

Gavin

 

[SkipVought]

I felt sorry for you.

Paste this into a MODULE in the VB Editor and run. It will combine all your data into one sheet, PROVIDED THAT each sheet has...
[tt]
' 1) the same number of columns of data
' 2) one row of headings in row 1
' 3) each corresponding column of data has the same TYPE of data on each sheet
[/tt]

Sub CombineAllSheets()
    Dim ws As Worksheet, sSQL As String, iCnt As Integer, sConn As String
    
'assuming that ALL sheets have
' 1) the same number of columns of data
' 2) one row of headings in row 1
' 3) each corresponding column of data has the same TYPE of data on each sheet
' then the query will consolidate the data into the Summary sheet

    On Error Resume Next
    If IsError(Sheets("Summary")) Then Worksheets.Add.Name = "Summary"
    On Error GoTo 0
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & ThisWorkbook.Path & "\" & Split(ThisWorkbook.Name, ".")(0) & ".xls;"
    sConn = sConn & "DefaultDir=" & ThisWorkbook.Path & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            iCnt = iCnt + 1
            sSQL = sSQL & "Select *, '" & ws.Name & "' as Sheet"
            sSQL = sSQL & vbLf
            sSQL = sSQL & "From [" & ws.Name & "$]"
            sSQL = sSQL & vbLf
            If iCnt < Worksheets.Count - 1 Then
                sSQL = sSQL & "UNION ALL"
                sSQL = sSQL & vbLf
            End If
        End If
    Next
    Debug.Print sSQL
    
    With Sheets("Summary")
        If .QueryTables.Count = 0 Then
            With Sheets("Summary").QueryTables.Add(Connection:=sConn, _
                Destination:=Sheets("Summary").Range("A1"))
                .CommandText = sSQL
                .Name = "qryALL"
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .Refresh BackgroundQuery:=False
            End With
        Else
            With Sheets("Summary").QueryTables("qryALL")
                .Connection = sConn
                .CommandText = sSQL
                .Refresh BackgroundQuery:=False
            End With
        End If
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}