Hi,

I have a cell, say A1, with conditional formatting based on the contents of
the cell on the same row, say, 3 columns to the right, specified with a
relative reference ($D2). When I copy the cells with the conditional
formatting up and down, the cell that determines the formatting goes with it,
which is just what I want. The formatting for A5 is based on D5, etc.

But I want my users to also be able to cut/paste these cells if they wish
and in this case, the formatting does not follow. For example, If I cut cell
A1 and paste to A5, the formatting is still based on D1, not D5.

I would think there's a way using indirect references, but I'm unable to see
the answer.

Thanks,
Barnett

Put the $ around the row number as well (i.e. $D$1)

Does that answer your question?

--
Regards,
Dave


"barnett" wrote:

Hi,

I have a cell, say A1, with conditional formatting based on the contents of
the cell on the same row, say, 3 columns to the right, specified with a
relative reference ($D2). When I copy the cells with the conditional
formatting up and down, the cell that determines the formatting goes with it,
which is just what I want. The formatting for A5 is based on D5, etc.

But I want my users to also be able to cut/paste these cells if they wish
and in this case, the formatting does not follow. For example, If I cut cell
A1 and paste to A5, the formatting is still based on D1, not D5.

I would think there's a way using indirect references, but I'm unable to see
the answer.

Thanks,
Barnett

Hi Dave,

Actually, I want the opposite. I want the reference to stay relative even
when the user cuts and pastes.

I think putting the $ in front of the row would make it absolute even when
copying.

Barnett

"David Billigmeier" wrote:

Put the $ around the row number as well (i.e. $D$1)

Does that answer your question?

--
Regards,
Dave


"barnett" wrote:

Hi,

I have a cell, say A1, with conditional formatting based on the contents of
the cell on the same row, say, 3 columns to the right, specified with a
relative reference ($D2). When I copy the cells with the conditional
formatting up and down, the cell that determines the formatting goes with it,
which is just what I want. The formatting for A5 is based on D5, etc.

But I want my users to also be able to cut/paste these cells if they wish
and in this case, the formatting does not follow. For example, If I cut cell
A1 and paste to A5, the formatting is still based on D1, not D5.

I would think there's a way using indirect references, but I'm unable to see
the answer.

Thanks,
Barnett

Yes, you are correct that putting a $ in front of both the row and column
would make it an absolute reference. But from your wording I thought that's
what you wanted? You mentioned when a user copies A1, who's formatting
relies on D1, and pastes in A5, you still want the formatting to refer to
D1... in which case you will need a dollar sign around the row (i.e. D$1).
If you want the reference to REMAIN relative, you shouldn't put in any dollar
signs (i.e. D1).

Does that make sense or am I still not reading your question right?
--
Regards,
Dave


"barnett" wrote:

Hi Dave,

Actually, I want the opposite. I want the reference to stay relative even
when the user cuts and pastes.

I think putting the $ in front of the row would make it absolute even when
copying.

Barnett

"David Billigmeier" wrote:

Put the $ around the row number as well (i.e. $D$1)

Does that answer your question?

--
Regards,
Dave


"barnett" wrote:

Hi,

I have a cell, say A1, with conditional formatting based on the contents of
the cell on the same row, say, 3 columns to the right, specified with a
relative reference ($D2). When I copy the cells with the conditional
formatting up and down, the cell that determines the formatting goes with it,
which is just what I want. The formatting for A5 is based on D5, etc.

But I want my users to also be able to cut/paste these cells if they wish
and in this case, the formatting does not follow. For example, If I cut cell
A1 and paste to A5, the formatting is still based on D1, not D5.

I would think there's a way using indirect references, but I'm unable to see
the answer.

Thanks,
Barnett

Hi Dave,

Maybe I wasn't clear initially. I want the conditional formatting to be
based on the contents of the cell on the same row, but 3 columns over.
Formatting of A5 would be based on D5, formatting of A9 based on D9.

After I cut A5 and paste it to A9, I want the formatting of A9 to still be
based on D9 and the formatting of A5 (now empty) to be still based on D5.

I could think of a way to trap the cut/paste operations using a macro, but I
don't want to embed macros in the file for various reasons. Maybe I just need
to tell the users not to cut/paste only copy. Or just give up the formatting.

Barnett

"David Billigmeier" wrote:

Yes, you are correct that putting a $ in front of both the row and column
would make it an absolute reference. But from your wording I thought that's
what you wanted? You mentioned when a user copies A1, who's formatting
relies on D1, and pastes in A5, you still want the formatting to refer to
D1... in which case you will need a dollar sign around the row (i.e. D$1).
If you want the reference to REMAIN relative, you shouldn't put in any dollar
signs (i.e. D1).

Does that make sense or am I still not reading your question right?
--
Regards,
Dave


"barnett" wrote:

Hi Dave,

Actually, I want the opposite. I want the reference to stay relative even
when the user cuts and pastes.

I think putting the $ in front of the row would make it absolute even when
copying.

Barnett

"David Billigmeier" wrote:

Put the $ around the row number as well (i.e. $D$1)

Does that answer your question?

--
Regards,
Dave


"barnett" wrote:

Hi,

I have a cell, say A1, with conditional formatting based on the contents of
the cell on the same row, say, 3 columns to the right, specified with a
relative reference ($D2). When I copy the cells with the conditional
formatting up and down, the cell that determines the formatting goes with it,
which is just what I want. The formatting for A5 is based on D5, etc.

But I want my users to also be able to cut/paste these cells if they wish
and in this case, the formatting does not follow. For example, If I cut cell
A1 and paste to A5, the formatting is still based on D1, not D5.

I would think there's a way using indirect references, but I'm unable to see
the answer.

Thanks,
Barnett

I'm replying again so I can check the notify box, which I forgot to do
earlier, in case Dave or anyone else has a suggestion for me.

Incidentally, I might be able to get it to work if there were a function
that returned the cell's own address. So entering a formula like =MyAddress()
in A4 would return a string "A4", or better, a range.

Barnett

"barnett" wrote:

Hi Dave,

Maybe I wasn't clear initially. I want the conditional formatting to be
based on the contents of the cell on the same row, but 3 columns over.
Formatting of A5 would be based on D5, formatting of A9 based on D9.

After I cut A5 and paste it to A9, I want the formatting of A9 to still be
based on D9 and the formatting of A5 (now empty) to be still based on D5.

I could think of a way to trap the cut/paste operations using a macro, but I
don't want to embed macros in the file for various reasons. Maybe I just need
to tell the users not to cut/paste only copy. Or just give up the formatting.

Barnett

"David Billigmeier" wrote:

Yes, you are correct that putting a $ in front of both the row and column
would make it an absolute reference. But from your wording I thought that's
what you wanted? You mentioned when a user copies A1, who's formatting
relies on D1, and pastes in A5, you still want the formatting to refer to
D1... in which case you will need a dollar sign around the row (i.e. D$1).
If you want the reference to REMAIN relative, you shouldn't put in any dollar
signs (i.e. D1).

Does that make sense or am I still not reading your question right?
--
Regards,
Dave


"barnett" wrote:

Hi Dave,

Actually, I want the opposite. I want the reference to stay relative even
when the user cuts and pastes.

I think putting the $ in front of the row would make it absolute even when
copying.

Barnett

"David Billigmeier" wrote:

Put the $ around the row number as well (i.e. $D$1)

Does that answer your question?

--
Regards,
Dave


"barnett" wrote:

Hi,

I have a cell, say A1, with conditional formatting based on the contents of
the cell on the same row, say, 3 columns to the right, specified with a
relative reference ($D2). When I copy the cells with the conditional
formatting up and down, the cell that determines the formatting goes with it,
which is just what I want. The formatting for A5 is based on D5, etc.

But I want my users to also be able to cut/paste these cells if they wish
and in this case, the formatting does not follow. For example, If I cut cell
A1 and paste to A5, the formatting is still based on D1, not D5.

I would think there's a way using indirect references, but I'm unable to see
the answer.

Thanks,
Barnett

barnett -
If you make the formatting of A5 depend on the value in D5 (making sure you
don't have $ around D5), then you copy A5 and paste in C5, Excel will
automatically update the formula in C5 to reference F5.

Another way you can achieve this is go to Tools-Options-'General' Tab,
check "R1C1 reference style". Then, assume you want to make the value in
each cell orange if the value in the cell in the same row and 3 columns over
is equal to 4, paste this code in the conditional formatting box for any cell
and change the formatting to orange:

=RC[3]=4

Referring to your question you just asked about a formula to return the cell
you are currently in, use:

=CELL("address")

--
Regards,
Dave


"barnett" wrote:

I'm replying again so I can check the notify box, which I forgot to do
earlier, in case Dave or anyone else has a suggestion for me.

Incidentally, I might be able to get it to work if there were a function
that returned the cell's own address. So entering a formula like =MyAddress()
in A4 would return a string "A4", or better, a range.

Barnett

"barnett" wrote:

Hi Dave,

Maybe I wasn't clear initially. I want the conditional formatting to be
based on the contents of the cell on the same row, but 3 columns over.
Formatting of A5 would be based on D5, formatting of A9 based on D9.

After I cut A5 and paste it to A9, I want the formatting of A9 to still be
based on D9 and the formatting of A5 (now empty) to be still based on D5.

I could think of a way to trap the cut/paste operations using a macro, but I
don't want to embed macros in the file for various reasons. Maybe I just need
to tell the users not to cut/paste only copy. Or just give up the formatting.

Barnett

"David Billigmeier" wrote:

Yes, you are correct that putting a $ in front of both the row and column
would make it an absolute reference. But from your wording I thought that's
what you wanted? You mentioned when a user copies A1, who's formatting
relies on D1, and pastes in A5, you still want the formatting to refer to
D1... in which case you will need a dollar sign around the row (i.e. D$1).
If you want the reference to REMAIN relative, you shouldn't put in any dollar
signs (i.e. D1).

Does that make sense or am I still not reading your question right?
--
Regards,
Dave


"barnett" wrote:

Hi Dave,

Actually, I want the opposite. I want the reference to stay relative even
when the user cuts and pastes.

I think putting the $ in front of the row would make it absolute even when
copying.

Barnett

"David Billigmeier" wrote:

Put the $ around the row number as well (i.e. $D$1)

Does that answer your question?

--
Regards,
Dave


"barnett" wrote:

Hi,

I have a cell, say A1, with conditional formatting based on the contents of
the cell on the same row, say, 3 columns to the right, specified with a
relative reference ($D2). When I copy the cells with the conditional
formatting up and down, the cell that determines the formatting goes with it,
which is just what I want. The formatting for A5 is based on D5, etc.

But I want my users to also be able to cut/paste these cells if they wish
and in this case, the formatting does not follow. For example, If I cut cell
A1 and paste to A5, the formatting is still based on D1, not D5.

I would think there's a way using indirect references, but I'm unable to see
the answer.

Thanks,
Barnett

Hi Dave,

Thanks for sticking it out with me on this.

I tried your R1C1 suggestion but as you indicated, it's not really any
different than using the relative reference with no $. Cutting and pasting
cells still doesn't update the reference target, while copying and pasting
still does.

And I tried the referencing the cell using
=Offset(Indirect(CELL("address")),0,4), but value returned by CELL("Address")
updates each time you select a cell anywhere (even other workbooks).

I do appreciate your efforts to stick with it. I went ahead and told users
not to cut|paste or move cells by dragging within my sheet. It shoudn't be
too much of a hardship.

Barnett



"David Billigmeier" wrote:

barnett -
If you make the formatting of A5 depend on the value in D5 (making sure you
don't have $ around D5), then you copy A5 and paste in C5, Excel will
automatically update the formula in C5 to reference F5.

Another way you can achieve this is go to Tools-Options-'General' Tab,
check "R1C1 reference style". Then, assume you want to make the value in
each cell orange if the value in the cell in the same row and 3 columns over
is equal to 4, paste this code in the conditional formatting box for any cell
and change the formatting to orange:

=RC[3]=4

Referring to your question you just asked about a formula to return the cell
you are currently in, use:

=CELL("address")

--
Regards,
Dave


"barnett" wrote:

I'm replying again so I can check the notify box, which I forgot to do
earlier, in case Dave or anyone else has a suggestion for me.

Incidentally, I might be able to get it to work if there were a function
that returned the cell's own address. So entering a formula like =MyAddress()
in A4 would return a string "A4", or better, a range.

Barnett

"barnett" wrote:

Hi Dave,

Maybe I wasn't clear initially. I want the conditional formatting to be
based on the contents of the cell on the same row, but 3 columns over.
Formatting of A5 would be based on D5, formatting of A9 based on D9.

After I cut A5 and paste it to A9, I want the formatting of A9 to still be
based on D9 and the formatting of A5 (now empty) to be still based on D5.

I could think of a way to trap the cut/paste operations using a macro, but I
don't want to embed macros in the file for various reasons. Maybe I just need
to tell the users not to cut/paste only copy. Or just give up the formatting.

Barnett

"David Billigmeier" wrote:

Yes, you are correct that putting a $ in front of both the row and column
would make it an absolute reference. But from your wording I thought that's
what you wanted? You mentioned when a user copies A1, who's formatting
relies on D1, and pastes in A5, you still want the formatting to refer to
D1... in which case you will need a dollar sign around the row (i.e. D$1).
If you want the reference to REMAIN relative, you shouldn't put in any dollar
signs (i.e. D1).

Does that make sense or am I still not reading your question right?
--
Regards,
Dave


"barnett" wrote:

Hi Dave,

Actually, I want the opposite. I want the reference to stay relative even
when the user cuts and pastes.

I think putting the $ in front of the row would make it absolute even when
copying.

Barnett

"David Billigmeier" wrote:

Put the $ around the row number as well (i.e. $D$1)

Does that answer your question?

--
Regards,
Dave


"barnett" wrote:

Hi,

I have a cell, say A1, with conditional formatting based on the contents of
the cell on the same row, say, 3 columns to the right, specified with a
relative reference ($D2). When I copy the cells with the conditional
formatting up and down, the cell that determines the formatting goes with it,
which is just what I want. The formatting for A5 is based on D5, etc.

But I want my users to also be able to cut/paste these cells if they wish
and in this case, the formatting does not follow. For example, If I cut cell
A1 and paste to A5, the formatting is still based on D1, not D5.

I would think there's a way using indirect references, but I'm unable to see
the answer.

Thanks,
Barnett