Office Connector Web Help | Online Help |
OCFilter Function |
Contents | Index |
PurposeThe OCFilter function is useful to work around the maximum forumla length limit of Excel by allowing you to break a single formula into smaller pieces. The OCFilter function was originally created to address Office Connector formulas with complex filter arguments, but it can be used for any text argument in any Excel formula.SyntaxOCFilter( filter template , enclosers , arg1 , [ ... argn ] )The OCFilter function syntax has these parts:
RemarksReplacement markers refer to the ordinal position of the associated argument. Use the enclosers to identify that a number N is a marker for the Nth argument. For example,OCFilter("My [1] has [2].", "[]", "dog", "fleas") returns My dog has fleas. because the enclosers argument indicates that any number enclosed in square brackets [ ] is a marker that refers to one of the args, so [1] refers to dog and [2] refers to fleas. Filter ExampleThis example demonstrates how you could use the OCFilter function to return a filter that matches records where the cost code is 1-045 and the job number is specified for each row in column A. You could then use the returned value as the filter argument in other functions like TSSum and TSCount.OCFilter("PJOB='[1]' AND [PHASE]='[2]'", "[]", $A1, "1-045") If cell $A1 contains 03-001, then the formula above returns: PJOB='03-001' AND PHASE='1-045' Inserting a FormulaTo enter an OCFilter function, click on the button (to the left of the Excel formula bar), select the Office Connector 2.x category, select OCFilter and click . You can then type the FilterTemplate, the enclosers and type values or click on cells for each of the args.See Also |