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
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
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-
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
The one subtle difference is the use of SHEET() instead of SHEETS() to return
the current worksheet.
Using the ISREF
ISREF Function to Check a Reference
Function to Check a Reference
The ISREF function tests whether a value is a reference.
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