Annoying Excel update values request

December 22, 2009 at 14:24:48
Specs: Windows XP

I want to total the data from four Excel worksheets into one total worksheet. When referencing a cell of the individual worksheets (e.g. Sheet2005!B9) I keep getting a request to update the values and select the name of the workbook for every entry (everything is in one workbook). I didn't have to go through this before I had to reinstall Excel 2003. I have been unable to find out anything on this.

See More: Annoying Excel update values request

Report •


#1
December 23, 2009 at 15:04:16

Hi,

Assuming Excel is not corrupt, this response occurs because Excel cannot find the worksheet named in the formula in the current workbook. (OK I know thats pretty obvious - but bear with me)

I have written a short macro which will test each cell in a selected range to see if the sheet name in each formula exactly matches one of the worksheets in your workbook.

In the VBA window (Alt+f11) insert a new module (not a class module).
Enter this code:

Option Explicit
Sub CompTabNames()
Dim n As Integer
Dim m As Integer
Dim strSrcArry() As String
Dim rngCell As Range
Dim wsEach As Worksheet
Dim blnMatch As Boolean

'get number of selected cells
n = Selection.Cells.Count
'resize array to match
ReDim strSrcArry(n, 1)
n = 0

For Each rngCell In Selection
    'put sheet names of cell references into array
    'flag which array members have a formula
    If rngCell.HasFormula = True Then
        strSrcArry(n, 0) = "F"
        strSrcArry(n, 1) = Mid(rngCell.Formula, 2, InStr(2, rngCell.Formula, "!") - 2)
        n = n + 1
        Else
        strSrcArry(n, 0) = "NF"
        n = n + 1
    End If
Next rngCell

For Each wsEach In ActiveWorkbook.Worksheets
    blnMatch = False
    For n = 0 To UBound(strSrcArry, 1)
     If strSrcArry(n, 0) = "F" Then
        If strSrcArry(n, 1) = wsEach.Name Then
            blnMatch = True
            MsgBox strSrcArry(n, 1) & " matches this Tab: " & wsEach.Name
        End If
     End If
     If blnMatch = True Then Exit For
    Next n
    If blnMatch = False Then
        MsgBox "No Match for Tab: " & wsEach.Name
    End If
Next wsEach
End Sub
Go back to your worksheet with the formulas and select a group of cells containing the linking formulas (=Sheet2!A1 etc)

Now run the Macro (back in the VBA window, click anywhere inside the subroutine and hit f5)

The macro will either give you a series of matching messages or one or more No match messages.

If you get matching messages, you will know that there are no subtle, non-visible differences between your formula sheet names and the names on the Tabs.

If all the formulas result in Matches then it is likely that Excel is corrupt and a repair is called for.

If you get No match messages, then a more detailed look at the formulas and worksheet names is called for.

Hope this helps

Regards


Report •

#2
December 24, 2009 at 09:07:41

Thanks for responding Humar. I found the answer after a lot of screaming and hair pulling. The syntax that works is '2005'!B9. Something about the fact that the sheet name is a number.

Report •

#3
December 24, 2009 at 09:37:50

Thanks for the follow-up

Regards

Humar


Report •

Related Solutions


Ask Question