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.

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 SubGo 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

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.

Thanks for the follow-up Regards

Humar

Ask Your Question

Weekly Poll

What do you think of Volkswagen's diesel settlement?

Discuss in The Lounge

Poll History