Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
relative references when cutting/pasting
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want chart source data to be relative references, not absolute. | Charts and Charting in Excel | |||
Relative Cell References within VBA code | Excel Discussion (Misc queries) | |||
macro vba relative references | Excel Worksheet Functions | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
How can I enable the "Record Using Relative References" option in. | Excel Discussion (Misc queries) |