R1C1 reference style

  • Thread starter Thread starter Guest
  • Start date Start date
Each cell is referenced by its column name and then row number. So the first
cell is called A1.

The R1C1 is the Reference style. Each cell is referenced by its row number
and column number. So the first cell is called R1C1.

Now some people may think that “E3″ is more intuitive than “R3C5″, and I can
probably agree with them when the spreadsheet is small. But when the
spreadsheet gets big and spans a couple hundred columns, I cannot contemplate
seeing “BK439″…

Simply an option
 
Sometimes you have to, sometimes it is more convenient.

Say you are looping through rows, and you are using a numeric variable to
track the row number. If you want to use that in a formula, it is easier to
use R1C1 notation where the column is numeric, not a column letter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks Bob

Terry

Bob Phillips said:
Sometimes you have to, sometimes it is more convenient.

Say you are looping through rows, and you are using a numeric variable to
track the row number. If you want to use that in a formula, it is easier to
use R1C1 notation where the column is numeric, not a column letter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
I use A1 almost all the time. I'll sometimes use R1C1 if I'm creating a
formulaR1C1 in VBA.

But one nice thing about using R1C1 reference style is when you're reviewing a
worksheet to make sure all of the formulas are consistent. (Without using
xl2002+'s error checking!)

Create a new worksheet with two sheets:
put this in E1
=IF(A1=5,"ok",VLOOKUP(A1,Sheet2!A:E,3,FALSE))
Drag it down 20 rows (say)

Now your job is to look at those 20 formulas to see if each of them is
consistent. So you turn the view to Formulas (tools|options|view tab|check
formulas).

You'll see something like:
=IF(A1=5,"ok",VLOOKUP(A1,Sheet2!A:E,3,FALSE))
=IF(A2=5,"ok",VLOOKUP(A2,Sheet2!A:E,3,FALSE))
=IF(A3=5,"ok",VLOOKUP(A3,Sheet2!A:E,3,FALSE))
=IF(A4=5,"ok",VLOOKUP(A4,Sheet2!A:E,3,FALSE))
=IF(A5=5,"ok",VLOOKUP(A5,Sheet2!A:E,3,FALSE))
=IF(A6=5,"ok",VLOOKUP(A6,Sheet2!A:E,3,FALSE))

And things look ok, but you can see that with a long formula (that wraps a few
times), it can get messy.

Now look at the same formulas with R1C1 checked:

And you see this:
=IF(RC[-4]=5,"ok",VLOOKUP(RC[-4],Sheet2!C[-4]:C,3,FALSE))
=IF(RC[-4]=5,"ok",VLOOKUP(RC[-4],Sheet2!C[-4]:C,3,FALSE))
=IF(RC[-4]=5,"ok",VLOOKUP(RC[-4],Sheet2!C[-4]:C,3,FALSE))
=IF(RC[-4]=5,"ok",VLOOKUP(RC[-4],Sheet2!C[-4]:C,3,FALSE))
=IF(RC[-4]=5,"ok",VLOOKUP(RC[-4],Sheet2!C[-4]:C,3,FALSE))
=IF(RC[-4]=5,"ok",VLOOKUP(RC[-4],Sheet2!C[-4]:C,3,FALSE))

I think it's easier to pick out the formula that doesn't belong!
 
Back
Top