Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching the formula (not the value) for a text string
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching the formula (not the value) for a text string
wrote:
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. 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),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching String for phrases/multiple text strings | Excel Worksheet Functions | |||
Searching for a string of text inside a column | Excel Worksheet Functions | |||
Formula for searching for a text string | Excel Discussion (Misc queries) | |||
Searching a text string in a range of cells. | Excel Worksheet Functions | |||
searching cells for a string of text | Excel Programming |