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
Search JabSto ::




Custom Search