Microsoft Office Tutorials and References

In Depth Information

**Using the ISREF Function to Check a Reference**

It would be useful to build a formula that sums all worksheets or sums all

worksheets up to the current worksheet. Formulas in B9, B12, and B15 show

how to build a text reference or a text formula that would work. Unfortu-

nately, the INDIRECT function fails when you pass it a 3-D reference, as in

B11.

The workaround is to use an ancient XL4 Macro Language function called

EVALUATE. This function is not a worksheet function. You won
’
t be able

to enter =EVALUATE in a worksheet cell and have it calculate correctly.

However, these old XL4 Macro functions may be entered in defined names

and will calculate correctly. Define a name such as Eval3D and use

=EVALUATE("=SUM(Jan:"&TEXT(DATE(2015,SHEETS(),1),"MMM")&"!$A$1)")

as the Refers To. This formula builds a SUM function on the fly. The

EVALUATE function returns the result of the formula. You can then enter

=Eval3D as shown in cell B14. Because each worksheet contains 100 in cell

A1, the formula is returning the correct total of 500 from the five work-

sheets.

A similar approach is used in B16. This formula returns the total from

January to the current worksheet, hence the 400 to total January through

April. Here, the name refers to

=EVALUATE("=SUM(Jan:"&TEXT(DATE(2015,SHEET(),1),"MMM")&"!$A$1)").

The one subtle difference is the use of SHEET() instead of SHEETS() to return

the current worksheet.

Using the

Using the
ISREF

ISREF
Function to Check a Reference

Function to Check a Reference

The ISREF function tests whether a value is a reference.

Syntax

ISREF(value)

ISREF returns TRUE if the value is a valid reference. Initially, this func-

tion might seem to be useless. After all, inherently you know that A2 is a

valid reference, so you would not have to use a function to test it.

The following formulas return TRUE: =ISREF(A2), =ISREF(XFD1048576), and

=ISREF(A2:Z99). The following formulas return FALSE: =ISREF("A2"),

=ISREF(99), and =ISREF(2+2).

ISREF is useful in one special circumstance. For example, suppose you have

designed a spreadsheet with the named range ExpenseTotal. If you are worried

that someone might have deleted this particular row, you can check whether