Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Filtering on a formula
I have a column which has inconsistent formulae running down it. Is there any method of filtering the data dependent on the phrasing of the formula? Or can I somehow refer to the text of the formula in another formula? -- Timmy Mac1 ------------------------------------------------------------------------ Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188 View this thread: http://www.excelforum.com/showthread...hreadid=483607 |
#2
|
|||
|
|||
Filtering on a formula
One quick and dirty way to check for formula consistency is to go into R1C1
reference style. Tools|Options|General Tab|Check R1C1 reference style. Then show the formulas: tools|options|view tab|check formulas. You can just scroll up/down looking for differences. In fact, if you're using xl2002+, you can turn on an error check. Tools|Options|error checking tab|make sure "inconsistent formula in region" is checked. You could also use a userdefined function to return the formula in an adjacent column (insert one right next to it). Here's one I use: Option Explicit Function GetFormula(Rng As Range) Dim myFormula As String GetFormula = "" With Rng.Cells(1) If .HasFormula Then If Application.ReferenceStyle = xlA1 Then myFormula = .Formula Else myFormula = .FormulaR1C1 End If If .HasArray Then GetFormula = "{=" & Mid(myFormula, 2, Len(myFormula)) & "}" Else GetFormula = myFormula End If End If End With End Function If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =getformula(a1) Timmy Mac1 wrote: I have a column which has inconsistent formulae running down it. Is there any method of filtering the data dependent on the phrasing of the formula? Or can I somehow refer to the text of the formula in another formula? -- Timmy Mac1 ------------------------------------------------------------------------ Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188 View this thread: http://www.excelforum.com/showthread...hreadid=483607 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing a formula for a colored value | New Users to Excel | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |