Evaluating Formulas – Support Topics

Overview

There are some cases where you want to evaluate a function only once each time you run your report.

A good example of this is the Now() function. You generally want the Now() function to run once when you update the report, and otherwise remain inert. If Now() is allowed to behave normally, it will cause the sheet it is on (including Jet formulas that sheet contains), to recalculate constantly, resulting in a major performance problem.

The NP function

You can use the NP function to control when a formula updates by putting "Eval" in the What parameter. The formula you want to evaluate, including the "=" sign goes in the Arg1 parameter and must be in quotes. If you are evaluating a formula that already has quotes inside it, then Excel requires you to use two sets of quotes instead of one. Some examples are listed below.

This following function will calculate Now() once each time the report updates.
```
=NP("Eval","=Now()")
```
The function below calculates a date filter for the current month without using NP("Eval").

=NP("Datefilter",Date(Year(Now()),Month(Now()),1),Date(Year(Now()),Month(Now())+1,1)-1)

However, the formula above uses the Now() function liberally, so you should put it inside an NP("Eval") formula and change "Datefilter" to ""Datefilter"" with two sets of quotes as in the following formula.
```
=NP("Eval","=NP(""Datefilter"",Date(Year(Now()),Month(Now()),1),Date(Year(Now()),Month(Now())+1,1)-1)")
```

Note that you should place any volatile Excel function (i.e. NOW() and TODAY()) directly inside the NP("Eval") to avoid constant recalculation, even if the volatile function has no other dependent cells. A cell reference is not sufficient.

For instance, =NP("Eval","=NOW()") will only be calculated once with each run of the report, whereas =NP("Eval","=B4"), where B4 contains a volatile function, will still be constantly recalculated.

Evaluating Off-sheet References

The NP ("Eval") function can evaluate any Excel function including cell references. The formula below displays the value in cell C5.
```
=NP("Eval","=C5")
```
This application of the Eval function can improve your report execution time if you have an Options sheet. When your report runs, any cell which has a dependency on a cell that has changed will be recalculated. Unfortunately, when cells have dependencies that extend outside their sheet, Excel does not know when that external reference may have changed so it will recalculate the formula every time the current worksheet changes. Jet Reports changes worksheets many times over the course of evaluating a report causing Excel to recalculate any formula that has off-sheet dependencies. You can eliminate this unnecessary recalculation by using the NP("Eval") function to bring the values from off-sheet references onto the local worksheet, then change your cell references to only use the local cell where you have the NP("Eval") function.
When you need to reference cells on other worksheets inside your workbook, you can use the same formula only with the worksheet name followed by an "!" as part of your cell reference. The following Eval function will display the value from C5 of the worksheet named "Options".
```
=NP("Eval","=Options!C5")
```
If the cell you are trying to reference is on a worksheet that has a space in its name you need to wrap the worksheet name in single quotes as in the following formula.
```
=NP("Eval","='Options Page'!C5")
```

Named ranges with NP("Eval")

NP("Eval") can be used with named ranges as well as cell references (see the Useful Excel Features section for information on Named Ranges).

So assuming you named cell C5 on your Options worksheet "DateFilter", you can use the cell's name as if it were a normal cell reference, without the worksheet or workbook name as in the following formula.
```
=NP("Eval","=DateFilter")
```
If you had a Profit and Loss report, you would probably have a changeable Start Date and End Date on the Options page with cell references on a Detail page and a Summary page. Instead of having many cell references to the Options page, you could name the cells StartDate and EndDate respectively, and use the following two formulas to bring the data in the two cells onto the Detail and Summary pages.
```
=NP("Eval","=StartDate")
```
```
=NP("Eval","=EndDate")
```

If you put these two formulas in cells B2 and B3 respectively, you can then use local cell references to B2 and B3 for your filters, eliminating a large amount of Excel calculation overhead when reporting.

Using NL("Sheets") with NP("Eval")

The NP("Eval") function will work to reference an Options page from a worksheet that is replicated with NL ("Sheets"). However, it may behave unexpectedly when it is used to reference a cell on the same sheet as the NL("Sheets") formula. Therefore, using an NP("Eval") formula on a sheet with an NL("Sheets") to reference cells that are on that same worksheet is not recommended.

Updated April 28, 2022 16:56: Jet Reports Historic Posts

Overview

The NP function

This following function will calculate Now() once each time the report updates.

The function below calculates a date filter for the current month without using NP("Eval").

However, the formula above uses the Now() function liberally, so you should put it inside an NP("Eval") formula and change "Datefilter" to ""Datefilter"" with two sets of quotes as in the following formula.

Evaluating Off-sheet References

The NP ("Eval") function can evaluate any Excel function including cell references. The formula below displays the value in cell C5.

When you need to reference cells on other worksheets inside your workbook, you can use the same formula only with the worksheet name followed by an "!" as part of your cell reference. The following Eval function will display the value from C5 of the worksheet named "Options".

If the cell you are trying to reference is on a worksheet that has a space in its name you need to wrap the worksheet name in single quotes as in the following formula.

Named ranges with NP("Eval")

So assuming you named cell C5 on your Options worksheet "DateFilter", you can use the cell's name as if it were a normal cell reference, without the worksheet or workbook name as in the following formula.

Using NL("Sheets") with NP("Eval")

Comments