Microsoft Office Tutorials and References
In Depth Information
LEARN R1C1 REFERENCES
Tip: After adding the formulas shown in Figure 45, insert two blank rows above
row 1. Cut the formulas in the total row and paste to the new row 1. After you
do this, your ad hoc totals are always visible near the headings.
Summary: You can use variations of the SUBTOTAL function to ignore hidden
rows.
LEARN R1C1 REFERENCES
Challenge: R1C1 cell referencing comes in handy in several situations, such
as in VBA, when you’re using INDIRECT , and with conditional formatting. It isn’t
hard to learn.
Background: VisiCalc introduced the A1 naming style for cells. Lotus 1-2-3
used the same system. With the Multiplan product, Excel used a system in
which columns were numbered. The cell that you and I know as D10 would
have been referred to as R10C4 in Multiplan (for row 10, column 4).
During the spreadsheet wars, Microsoft realized that most of the world was used
to A1 referencing, and to compete, it would have to pretend that Excel used
A1 references. Of course, Excel only pretends to use A1. It really uses R1C1
behind the scenes. If you don’t believe me, in Excel 2003 select Tools, Options,
Generaal, R1C1 style as shown in Figure 46. In Excel 2007, use Offi ce Icon,
Excel Options, Formulas, Working with Formulas, R1C1 Reference Style..
Part
I
Figure 46. You are one click away from R1C1-style
spreadsheets.
 
 
Search JabSto ::




Custom Search