RC format reference

[Alan Beban]

I have the following question from a colleague:

I like to solve the following problem. I have an RC address in one cell
of the form, RC6, (current row, col 6). I like to copy and paste this to
another cell (or copy the conditional format portion involving a formula
of the above address form), but the address in the new place to reflect
its new row, that is, the address should remain as RC6 in the new place.
When I do the copy and paste, the row address changes to an absolute
address of the form R[-4]C6, which is the correct behaviour by the above
definition.

But if I start with a relative R[0]C6, the only form I know for a
relative row format, when copied it gets converted into an absolute RC6
at the new place. Is there a way to get the effect I want, or is this a
known bug (ala feature as defined by MS) in Excel 2000 (9.0.2720)?

TIA,
Alan Beban

 

[T. Valko]

Not sure I understand. Maybe this will work:

INDIRECT("RC6",0)

It's relative to the row and the column is absolute.

 

[Harlan Grove]

. . . I have an RC address in one cell of the form, RC6, (current
row, col 6). . . .

This is a column-absolute, row-relative reference. If it were in, say,
cell X99, it'd be equivalent to the A1-syntax reference $F99.

. . . I like to copy and paste this to another cell (or copy the
conditional format portion involving a formula of the above address
form), but the address in the new place to reflect its new row, that
is, the address should remain as RC6 in the new place. . . .

Note you use the verb COPY. Did you friend use this PRECISELY verb?
Not CUT, MOVE, DRAG or something else?

Under Excel 2003 SP1, with R1C1 range reference syntax in use, I enter
the following formula into cell H3, er, R3C8.

=ISBLANK(RC6)

I then define 3 formula conditional formats.

1. =ISERROR(RC6) produces red background color.
2. =ISTEXT(RC6) produces yellow background color.
3. =ISNUMBER(RC6) produces green background color.

Then I fill this cell into the 3 cells immediately below it. The cell
formulas appear to be identical in each, and so do the conditional
format formulas.

. . . When I do the copy and paste, the row address changes to an
absolute address of the form R[-4]C6, . . .

You should have been able to correct this misunderstanding. This is
STILL a column-absolute, row-relative reference, but instead of
referring to the cell in column 6 in the same row, it's referring to
the cell in column 6 in the row 4 above the row that contains the cell
or conditional formatting formula that contains this reference.

If I drag the 4 cells I have in use so far down 4 rows (and across any
number of columns), the references in the formulas change to R[-4]C6.
CUTTING and pasting would produce the same results.

. . . which is the correct behaviour by the above definition. . . .

Only if your friend means CUT, but not COPY.

. . . But if I start with a relative R[0]C6, . . .

You can enter this, but Excel will IMMEDIATELY convert it to RC6.

. . . the only form I know for a relative row format, . . .

We have some deep misunderstanding of R1C1 reference syntax here!

. . . when copied it gets converted into an absolute RC6 . . .

THIS IS NOT AN ABSOLUTE REFERENCE. As I just stated above, Excel has
always, does now and very, very likely will always treat R[0]C6 as
exactly the same reference as RC6, much as it treats =1+2 and =1+2+0
as exactly the same.

. . . at the new place. Is there a way to get the effect I want, or
is this a known bug (ala feature as defined by MS) in Excel 2000
(9.0.2720)?

The only error here is this person's lack of understanding about R1C1
reference syntax and his/her extremely likely misuse of the word COPY
when s/he means CUT.