Could anyone briefly tell me why you would use a R1C1 Ref please

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




"Helpme Please" wrote:

Could anyone briefly tell me why you would use a R1C1 Ref please

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)

"Helpme Please" wrote in message
...
Could anyone briefly tell me why you would use a R1C1 Ref please

Thanks very much for that , Appreciate your time and trouble in replying
"N.F" wrote:

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




"Helpme Please" wrote:

Could anyone briefly tell me why you would use a R1C1 Ref please

Thanks Bob

Terry

"Bob Phillips" wrote:

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)

"Helpme Please" wrote in message
...
Could anyone briefly tell me why you would use a R1C1 Ref please

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!


Helpme Please wrote:

Could anyone briefly tell me why you would use a R1C1 Ref please

--

Dave Peterson