When you say "Is there a way to determine/check that

"=SUM(Sheet1:Sheet4!A1)" formula indeed uses a cell from Sheet2?", I

assume what you mean is "Does the formula actually add a number from

A1 in all of those sheets?". The formula uses a cell from Sheet2

(provided Sheet2 is in the range Sheet1:Sheet4), but it might not SUM

that cell if the cell is not a number. If there is text in a cell

within that range, the sum function will ignore it, and if there is an

error in A1 in one of those sheets, the SUM function will return an

error.

Now, making the assumption that you have a formula in the form

"=SUM(Sheet1:Sheet4!A1)", you can use a UDF to check that all of the

A1 cells in those sheets have numbers (and thus are included in the

sum function).

A sample function follows (keep in mind this is a very specific

function and will not work for all types of equations, only when there

is a FUNCTION(sheetX:SheetY!rangeZ). If you need something to check

more types of functions, let me know (Following this method makes it

fairly complicated).

Function Check_Sum(rngFormula As Range) As Boolean

Dim strFormula As String

Dim strFirstSheet As String

Dim strLastSheet As String

Dim strRange As String

Dim iPosition As Integer

Dim iColonPosition As Integer

Dim sh As Worksheet

Check_Sum = False

strFormula = Replace(rngFormula.Formula, "'", vbNullString)

If InStr(strFormula, "!") <> 0 Then

iPosition = InStr(1, strFormula, "(") + 1

iColonPosition = InStr(1, strFormula, ":")

strFirstSheet = Mid(strFormula, iPosition, iColonPosition -

iPosition)

strLastSheet = Mid(strFormula, iColonPosition + 1, InStr(1,

strFormula, "!") - iColonPosition - 1)

strRange = Mid(strFormula, InStr(1, strFormula, "!") + 1, InStr(1,

strFormula, ")") - InStr(1, strFormula, "!") - 1)

End If

For Each sh In ActiveWorkbook.Sheets

If sh.Index >= Sheets(strFirstSheet).Index And sh.Index <=

Sheets(strLastSheet).Index Then

Select Case

Application.WorksheetFunction.IsNumber(sh.Range(strRange).Value)

Case True

Check_Sum = True

Case False

Check_Sum = False

Exit Function

End Select

End If

Next sh

End Function

This function will return TRUE/FALSE. If all of the A1 cells in the

formula have numbers, it will return TRUE, if even one is not a

number, it will return FALSE. If you want to return a list of the

sheets with non-numeric A1 ranges, the formula can be easily modified.

(If that's what you're looking for, let me know).

It's not a very nice silution, but I think it works. I hope it helps.