Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default If Statement Checking Formula NOT Value in a Cell

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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default If Statement Checking Formula NOT Value in a Cell

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.

--
Gary's Student
gsnu200702


"Marathon" wrote:

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default If Statement Checking Formula NOT Value in a Cell

This looks like much difficult than I thought! Thanks.

"Gary''s Student" wrote:

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.

--
Gary's Student
gsnu200702


"Marathon" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default If Statement Checking Formula NOT Value in a Cell

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
--
Gary's Student
gsnu200702


"Marathon" wrote:

This looks like much difficult than I thought! Thanks.

"Gary''s Student" wrote:

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.

--
Gary's Student
gsnu200702


"Marathon" wrote:

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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default If Statement Checking Formula NOT Value in a Cell

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.

"Gary''s Student" wrote:

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
--
Gary's Student
gsnu200702


"Marathon" wrote:

This looks like much difficult than I thought! Thanks.

"Gary''s Student" wrote:

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.

--
Gary's Student
gsnu200702


"Marathon" wrote:

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.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default If Statement Checking Formula NOT Value in a Cell

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.
--
Gary's Student
gsnu200702


"Marathon" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary's Student
gsnu200702


"Marathon" wrote:

This looks like much difficult than I thought! Thanks.

"Gary''s Student" wrote:

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.

--
Gary's Student
gsnu200702


"Marathon" wrote:

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.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 39
Default If Statement Checking Formula NOT Value in a Cell

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(st rRange).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.
On Jan 27, 8:50 pm, Marathon
wrote:
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.

"Gary''s Student" wrote:
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
--
Gary's Student
gsnu200702


"Marathon" wrote:


This looks like much difficult than I thought! Thanks.


"Gary''s Student" wrote:


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.


--
Gary's Student
gsnu200702


"Marathon" wrote:


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.


  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default If Statement Checking Formula NOT Value in a Cell

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.


"meatshield" wrote:

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(st rRange).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.
On Jan 27, 8:50 pm, Marathon
wrote:
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.

"Gary''s Student" wrote:
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
--
Gary's Student
gsnu200702


"Marathon" wrote:


This looks like much difficult than I thought! Thanks.


"Gary''s Student" wrote:


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.


--
Gary's Student
gsnu200702


"Marathon" wrote:


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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using an offset formula for the reference in a relative reference Cuda Excel Worksheet Functions 6 November 15th 06 06:12 PM
Can't add 7th IF statement to long formula. manxman Excel Worksheet Functions 7 June 8th 06 08:23 AM
assign formula to another cell Dannycol Excel Worksheet Functions 3 May 12th 06 09:46 PM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM


All times are GMT +1. The time now is 09:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"