Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Can't add 7th IF statement to long formula. | Excel Worksheet Functions | |||
assign formula to another cell | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions |