You can manually create formulas in A1 reference style or R1C1 reference style.
In xl2003 menus, you can toggle this setting on the:
tools|Options|General tab
If you create a formula using A1 reference style:
=sum(a:a)
In cell C1
and then switch to R1C1 reference style, you'll see numbers instead of letters
at the top of the columns. (This may be confusing, so remember how to toggle
this setting.)
But the formula changes to:
=SUM(C[-2])
C represents the Column. The stuff in [] indicates where (in relationship to
the cell with the formula) to find this column.
In this case, it says to go 2 columns to the left (column A in A1 reference
style).
It may be better to use single cells in a couple of test formulas.
Try putting:
=A1+A2 in B1
=$a1+$a2 in B2
=a$1+a$2 in B3
=$a$1+$a$2 in B4
(while in A1 reference style)
Then switch to R1C1 reference style and take a look at each of those cells with
the formulas:
You'll see:
=RC[-1]+R[1]C[-1]
RC[-1] means same row and one column to the left
=R[-1]C1+RC1
R[-1]C means one row up (positives are down) and column 1 (always column 1)
=R1C[-1]+R2C[-1]
R1C[-1] means row 1 (always) and the column to the left
=R1C1+R2C1
R1C1 means Row 1 and column 1 (A1) and R2C1 means row 2 and column 1 (A2)
For me, I rarely use R1C1 reference style when I'm doing things manually. But
there are times in code where populating a formula in a range of cells is much,
much easier using .formulaR1C1.
And just because you use .formula or .formular1c1 in code doesn't mean it
changes the way the user sees the formula--that's still specified by the option
they choose.
One of the nice features of using R1C1 is when you want to check the consistency
of your formulas in a single column.
Turn on R1C1 reference style.
View formulas (Tools|Options|view tab)
and every (consistent) formula in that range will look identical.
You'll be able to pick out the cell(s) with the formulas that have had some
tweaking done to them pretty easily.
As for the formula:
(in A1 reference style)
=sum($a:$a) or =sum(a:a)
or (in R1C1 reference style)
=sum(c1) or =sum(c[-2]) (only in column 3/C)
They all work against the used range. If you've only filled up rows 1-1000,
then excel knows what to look at. But by using this formula, you don't have to
adjust the formula when you add data to row 1001.
I am in the process of beginning to teach myself VBA for Excel 2007. The
primer I am using starts with recording macros, editing recorded macros, and
eventually moves on to writing code from scratch.
Question, I recorded a macro where I put a formula =sum(A:A) into cell C1.
The recorder coded this formula to
Range("C1").Select
ActiveCell.FormulaR1C1 = "=SUM(C[-2])"
I am not sure I understand the syntax of the sum function. I do believe it
is indicating to select data 2 columns tot he left of column C, but does it
assume the data populates all cells that column, in this example column A.
