Searching the formula (not the value) for a text string

  • Thread starter Thread starter sweetspiced
  • Start date Start date
S

sweetspiced

Basically I'm trying to add some checks to a table the gets updated on a regular basis.

The formulas for three of the cells contain dates:
=IFERROR(I14/ROUNDUP((("4/5/2014"-VLOOKUP($C$14,'Goals'!$A:$I,8,FALSE)) /(VLOOKUP($C$14,'Goals'!$A:$I,9,FALSE)-VLOOKUP($C$14,'Goals'!$A:$I,8,FALSE)))*VLOOKUP($C$14,'Goals'!$A:$I,2,FALSE),0),"")

as part of the check I'd like to compare today() with the string of text in the formula that is the date to make sure they've been updated.

I have a tab with all the validations and I've tried concatenating the formula cell with an ' to force it into a text value but that didn't work. Nor does trying to change it to a text value. Bahttext gave me some kind of hebrew text.

Any other options? I'd like it to be a formula not a VBA solution.

Thanks in advance.
 
Basically I'm trying to add some checks to a table the gets updated
on a regular basis. The formulas for three of the cells contain dates:
=IFERROR(I14/ROUNDUP((("4/5/2014"-VLOOKUP($C$14,'Goals'!$A:$I,8,FALSE))
/(VLOOKUP($C$14,'Goals'!$A:$I,9,FALSE)-VLOOKUP($C$14,'Goals'!$A:$I,8,FALSE)))
* VLOOKUP($C$14,'Goals'!$A:$I,2,FALSE),0),"")
as part of the check I'd like to compare today() with the string
of text in the formula that is the date to make sure they've
been updated. [....]
I'd like it to be a formula not a VBA solution.
Click to expand...

AFAIK, we cannot access the text of a formula in a cell without resorting to
VBA. If you want to minimize VBA usage, you could use the following
function to return the formula:

Function myFormula(r As Range) As String
myFormula = r(1).Formula
End Function

I use r(1) just in case r is not a single cell as expected.

Of course, then you must parse the result to extract the date string. That
is much easier and more efficient to do in VBA, if we are going to use VBA
anyway.

But why not opt for the simplest solution?

Put the date into a cell, for example X14 if you want a different date for
each row. Then the formula becomes:

=IFERROR(I14/ROUNDUP(((X14-VLOOKUP($C$14,'Goals'!$A:$I,8,FALSE))
/(VLOOKUP($C$14,'Goals'!$A:$I,9,FALSE)-VLOOKUP($C$14,'Goals'!$A:$I,8,FALSE))) * VLOOKUP($C$14,'Goals'!$A:$I,2,FALSE),0),"")(Use $X$14 instead of X14 if the same date will be used in all other copiesof the formula.)And your comparison with TODAY() could even be a Conditional Formatexpression.If your intent is to use TODAY() in the formula, you could put =TODAY() intoX14, or simply write:=IFERROR(I14/ROUNDUP(((TODAY()-VLOOKUP($C$14,'Goals'!$A:$I,8,FALSE)) /(VLOOKUP($C$14,'Goals'!$A:$I,9,FALSE)-VLOOKUP($C$14,'Goals'!$A:$I,8,FALSE))) * VLOOKUP($C$14,'Goals'!$A:$I,2,FALSE),0),"")
 
Back
Top