If Statement Checking Formula NOT Value in a Cell


G

Guest

Hi,

Is it possible to write an "IF" statement which checks not the value of the
cell, but whether the formula in cell gets data from a certain "Sheet".

Let say, in "A1" cell of the "Sheet4" I have this formula
"=Sheet1!A1+Sheet2!A1+Sheet3!A1". I would like to check whether the formula
in cell "A1" of "Sheet4" uses data from "Sheet2". Is this possible?

Thanks.
 
Ad

Advertisements

G

Guest

Try the small UDF:

Function from_whence(r As Range) As Boolean
Dim s As String
from_whence = False
s = r.Formula
If InStr(s, "Sheet2") > 0 Then
from_whence = True
End If
End Function

=from_whence(D1) returns False if D1 contains
=COUNTIF(A1:A100,"<20")-COUNTIF(A1:A100,"<9")
but will return True if D1 contains:
=COUNTIF(A1:A100,"<10")*Sheet2!A1

See:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

if you are not familiar with VBA.
 
G

Guest

Not difficult:

From Excel
ALT-F11
brings up VBE. In VBE
ALT-I
ALT-M
brings up a blank module. Just paste the stuff in the module and then close
VBE
 
G

Guest

This is great! Let me ask a slightly different situation:

In this case, I have Sheet1,Sheet2,Sheet3,Sheet3,Sheet4 and Sheet 5. In cell
A1 of Sheet5, I am writing "=SUM(Sheet1:Sheet4!A1)" to sum the numbers in A1
cells of Sheet1, Sheet2, Sheet3 and Sheet4, instead of writing mathematical
formula "=Sheet1!A1+Sheet2!A1+Sheet3!A1+Sheet4!A1".

Is there a way to determine/check that "=SUM(Sheet1:Sheet4!A1)" formula
indeed uses a cell from Sheet2?

Thanks for your help.
 
G

Guest

Now this IS difficult! Our previous technique of treating the formula as a
string and looking for a pattern just won't work here.

I am stumped.
 
Ad

Advertisements

M

meatshield

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.
 
Ad

Advertisements

G

Guest

Thanks a lot for you time and insight, "meatshield" and "Gary's Student". I
do have a much better feel for the problem on hand because of your thoughtful
explanations.

Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top