Identifying cell references used in a formula

[ChristineJ]

Cell F20 contains a formula such as =D7+D8+D9, =D7+D8+2100, or SUM(D7:D9).

I want to test FORMULATEXT(F20), the actual formula in F20, to determine (TRUE or FALSE) if all cell references used are within the range D6:D14.

For example, D7+D8+D9 would return TRUE. But D7+D8+K9 or D7+D8+D50 would return FALSE.

Any way to do this? Thanks!

 

[Peter_SSs]

Could you have a formula like this which sums cells entirely within the said range but has no direct cell references in that range?
If so, what result would you want?

=SUM(INDEX(D:D,8):INDEX(D:D,10))

 

[ChristineJ]

Could you have a formula like this which sums cells entirely within the said range but has no direct cell references in that range?
If so, what result would you want?

=SUM(INDEX(D:D,8):INDEX(D:D,10))

Thanks for the reply. Not all of the cells in range D6:D14 contain numbers - two are dates and two are text - so the SUM probably won't do it.

The context is that I have students and want to evaluate formulas they enter in F20 to verify that they did not use "helper cells" outside of range D6:D14, which is the data I provide them with.

I tried the code below. It works for the letters in the cell references and if they entered "SUM" in the formula. I'm having trouble with the numbers. With what I have below, I'm assuming they would not use helper cells after row 40, which is likely but not certain.

But the main failure comes if a student enters in something like this and uses a number: =D7+D8+2100 In this case it returns FALSE, indicating that a row outside of 6 through 14 was referenced because it sees the "21" in "2100". I want it to return TRUE since no helper cell was used. (FYI I have a separate comment in a different cell to tell them they should have used a cell reference rather than typing in "2100").

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"A","B","C","DD","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30""31","32","33","34","35","36","37","38","39","40"},SUBSTITUTE(FORMULATEXT(F20),"SUM",""))))>0,"FALSE","TRUE")

I'm stumped at how to deal with this. Thanks for your help.

 

[Peter_SSs]

That didn't really address what I was getting at.
Take this formula
=INDEX(B6:F11,2,3)+INDEX(C4:E14,6,2)

In this formula you can see 4 cell references all outside your range of interest. However, what the formula is actually doing is adding two cell, D7 and D9, both of which are inside your range of interest.

Another example might be the formula
=Sheet2!D9
The actual cell reference in this formula is D9 (like one of your cells) but is on a different sheet.

Having said that, looking back over the thread, I am guessing that you are only expecting fairly simple & straight-forward formulas?

In any case whatever is required, I would approach using vba. Is that acceptable?

 

[ChristineJ]

My example in my original post was misleading - it will not always be a formula involving a SUM, although that example was. It could include any function, or no function at all.

What I want to do is determine if any cell references were used in the formula outside of the range D6:D14.

Apologies for not being clearer. If this can be done. VBA is fine. C

 

[Peter_SSs]

What I want to do is determine if any cell references were used in the formula outside of the range D6:D14

Ok, so even though the formula near the top of post #4 only adds two numbers in the range D6:D14, you would want to to return FALSE because it used references outside that range to do it?

If so then you could try this user-defined function.

Function CheckRefs(MainRange As Range, FormulaCell As Range) As Boolean
  Dim RX As Object, M As Object
  Dim rng As Range
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\b[A-Z]{1,3}\d{1,7}(?=\D|$)"
  Set M = RX.Execute(FormulaCell.Formula)
  CheckRefs = True
  For Each M In RX.Execute(FormulaCell.Formula)
    On Error Resume Next
    Set rng = Range(M)
    On Error GoTo 0
    If Not rng Is Nothing Then
      If Intersect(MainRange, rng) Is Nothing Then
        CheckRefs = False
        Exit For
      ElseIf rng.Address <> Intersect(MainRange, rng).Address Then
        CheckRefs = False
        Exit For
      End If
    End If
  Next M
End Function

ChristineJ.xlsm
	D	E	F	G
6	1
7	2
8	3
9	4
10	5
11	6
12	7
13	8
14	9
15
16
17
18
19
20			9	TRUE
21			11	FALSE
22			5	FALSE
23			15	TRUE
24		x	5	FALSE
25			44	FALSE
Sheet1

Cell Formulas
Range	Formula
F20	F20	=D7+D8+D9
G20:G25	G20	=CheckRefs(D$6:D$14,F20)
F21	F21	= D7+D8+K9
F22	F22	=D7+D8+D56
F23	F23	=SUM(D6:D10)
F24	F24	=IF(E24="","",D10)
F25	F25	=SUM(D7:D19)

 

[ChristineJ]

Ok, so even though the formula near the top of post #4 only adds two numbers in the range D6:D14, you would want to to return FALSE because it used references outside that range to do it?

If so then you could try this user-defined function.

Function CheckRefs(MainRange As Range, FormulaCell As Range) As Boolean
  Dim RX As Object, M As Object
  Dim rng As Range
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\b[A-Z]{1,3}\d{1,7}(?=\D|$)"
  Set M = RX.Execute(FormulaCell.Formula)
  CheckRefs = True
  For Each M In RX.Execute(FormulaCell.Formula)
    On Error Resume Next
    Set rng = Range(M)
    On Error GoTo 0
    If Not rng Is Nothing Then
      If Intersect(MainRange, rng) Is Nothing Then
        CheckRefs = False
        Exit For
      ElseIf rng.Address <> Intersect(MainRange, rng).Address Then
        CheckRefs = False
        Exit For
      End If
    End If
  Next M
End Function

ChristineJ.xlsm
	D	E	F	G
6	1
7	2
8	3
9	4
10	5
11	6
12	7
13	8
14	9
15
16
17
18
19
20			9	TRUE
21			11	FALSE
22			5	FALSE
23			15	TRUE
24		x	5	FALSE
25			44	FALSE
Sheet1

Cell Formulas
Range	Formula
F20	F20	=D7+D8+D9
G20:G25	G20	=CheckRefs(D$6:D$14,F20)
F21	F21	= D7+D8+K9
F22	F22	=D7+D8+D56
F23	F23	=SUM(D6:D10)
F24	F24	=IF(E24="","",D10)
F25	F25	=SUM(D7:D19)

This is fantastic! Works perfectly and is so much more accurate and efficient than what I was attempting.

Thank you so much for sticking with me and providing this excellent solution!

 

[Peter_SSs]

You're welcome, but note that it does only look for simple cell references. For example (I should have included some like this) it returns True for
=D7+D8+$K$9
=D8+Sheet7!D8 (where Sheet7 is not the sheet that your D6:D14 range resides.)

Do we need to address that sort of situation?

 

[Peter_SSs]

Do we need to address that sort of situation?

If yes, then this modified function could be tested, though some types of references might still slip through (eg = INDEX(E:E,8) returns True when it should not)

Function CheckRefs(MainRange As Range, FormulaCell As Range) As Boolean
  Dim RX As Object, M As Object
  Dim rng As Range

  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?"
  CheckRefs = True
  For Each M In RX.Execute(FormulaCell.Formula)
    On Error Resume Next
    Set rng = Range(M)
    On Error GoTo 0
    If Not rng Is Nothing Then
      If InStr(1, M, "!") > 0 Then
        CheckRefs = False
        Exit For
      ElseIf Intersect(MainRange, rng) Is Nothing Then
        CheckRefs = False
        Exit For
      ElseIf rng.Address <> Intersect(MainRange, rng).Address Then
        CheckRefs = False
        Exit For
      End If
    End If
  Next M
End Function

ChristineJ.xlsm
	D	E	F	G
6	1
7	2
8	3
9	4
10	5
11	6
12	7
13	8
14	9
15
16
17
18
19
20			9	TRUE
21			11	FALSE
22			47	FALSE
23			15	TRUE
24		x	5	FALSE
25			44	FALSE
Sheet3

Cell Formulas
Range	Formula
F20	F20	=D7+D$8+$D$9
G20:G25	G20	=CheckRefs(D$6:D$14,F20)
F21	F21	=D7+D8+$K$9
F22	F22	=D8+Sheet7!D8
F23	F23	=SUM(D6:D10)
F24	F24	=IF(E24="","",D10)
F25	F25	=SUM(D7:D19)

 

[ChristineJ]

Thanks for thinking ahead on this for me.

1. The modified code does properly take care of absolute references with dollar signs, which I had not thought of. I need that.

2. Right now everything is contained on one sheet. Your formula =D8+Sheet7!D8 in F22 shows FALSE, which is fine. But =D8+Sheet1!D8 (the sheet I am on) also shows FALSE. Is there a way to allow Sheet1 (or CapCost, which the name of my sheet) to show as TRUE?

3. Right now it shows TRUE for range D6:D14. Could I have it show TRUE for an additional range as well, such as C18:G33?

The RX.Pattern in your code is fascinating - I am going to try to figure out the logic of what it is doing. I really appreciate your help on this! CJ