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.
