The quick answer is “Replace the existing value with a cell reference using the following syntax
“ & <cell address> & “
Following is a more detailed explanation: Start by creating the workbook function and using a hard-coded value for the filter (see the screen shot for JC transaction example below). That will create a function like this (the bold part represents the filter argument):
=TSSum("CURRENT_JCT_RECORD_1","TAMOUNT","((TACTDAT >= '01/01/2003') AND (TACTDAT <= '12/31/2003'))")
Now modify the filter argument by replacing the date values with cell references. You need everything except the date value itself. Supposing you had the begin and end dates in cells C2 and D2 respectively. Your filter argument will go from this:
"((TACTDAT >= '01/01/2003') AND (TACTDAT <= '12/31/2003'))"
To This:
"((TACTDAT >= '” & C2 & “') AND (TACTDAT <= '” & D2 & “'))"
The “trick” here is to get the syntax correct for joining strings together. The & operator concatenates strings, and the double quote defines the beginning or end of a string. Above we use the double quotes to break the filter string into pieces, replace the date value with a cell reference and use & to concatenate the results back into a single string.
Add to favorites