Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting and Cell References
I've worked out how to format a row based on one cell in the row but when I
try to copy that formula to my entire column every row's color is conditional to the value of the first cell, not to the cell for each respective row. (Same question as SlyGuy2183, but I could not see where he had received a satsifactory answer. My question is the same). I have no absolute references, i.e., no "$"s in my formula). Formula is "cell value (in N3) =K2, color it blue". When I extend that conditional formula to cell N3, it still refers to cell K2 instead of to cell K3. 'Suggestions? DOUG |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting and Cell References
Doug,
Select N2, then use Format / Conditional Formatting Cell Value is Greater than or equal to =K2 HTH, Bernie MS Excel MVP "DOUG ECKERT" wrote in message ... I've worked out how to format a row based on one cell in the row but when I try to copy that formula to my entire column every row's color is conditional to the value of the first cell, not to the cell for each respective row. (Same question as SlyGuy2183, but I could not see where he had received a satsifactory answer. My question is the same). I have no absolute references, i.e., no "$"s in my formula). Formula is "cell value (in N3) =K2, color it blue". When I extend that conditional formula to cell N3, it still refers to cell K2 instead of to cell K3. 'Suggestions? DOUG |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting and Cell References
Bernie: I can change the cell references one-by-one, but that is too labor
intensive. Is there not a way to adjust the formula automatically? I thought Paste SpecialFormulas would do so, but it maintains the original cell reference in the conditional formatting formula, without any adjustments as to the row. DOUG "Bernie Deitrick" wrote: Doug, Select N2, then use Format / Conditional Formatting Cell Value is Greater than or equal to =K2 HTH, Bernie MS Excel MVP "DOUG ECKERT" wrote in message ... I've worked out how to format a row based on one cell in the row but when I try to copy that formula to my entire column every row's color is conditional to the value of the first cell, not to the cell for each respective row. (Same question as SlyGuy2183, but I could not see where he had received a satsifactory answer. My question is the same). I have no absolute references, i.e., no "$"s in my formula). Formula is "cell value (in N3) =K2, color it blue". When I extend that conditional formula to cell N3, it still refers to cell K2 instead of to cell K3. 'Suggestions? DOUG |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting and Cell References
Doug,
I think we may be talking at cross purposes. Are you talking about formulas in the cell, or formulas in the Conditional Formatting? The CF cell references will automatically update to the correct row if the CF cell reference is =K2 and not =$K$2, or =K$2 (a fourth possibility is =$K2, but that would work for additional rows within the column) when you copy and paste formats (or paste all)... HTH, Bernie MS Excel MVP "DOUG ECKERT" wrote in message ... Bernie: I can change the cell references one-by-one, but that is too labor intensive. Is there not a way to adjust the formula automatically? I thought Paste SpecialFormulas would do so, but it maintains the original cell reference in the conditional formatting formula, without any adjustments as to the row. DOUG "Bernie Deitrick" wrote: Doug, Select N2, then use Format / Conditional Formatting Cell Value is Greater than or equal to =K2 HTH, Bernie MS Excel MVP "DOUG ECKERT" wrote in message ... I've worked out how to format a row based on one cell in the row but when I try to copy that formula to my entire column every row's color is conditional to the value of the first cell, not to the cell for each respective row. (Same question as SlyGuy2183, but I could not see where he had received a satsifactory answer. My question is the same). I have no absolute references, i.e., no "$"s in my formula). Formula is "cell value (in N3) =K2, color it blue". When I extend that conditional formula to cell N3, it still refers to cell K2 instead of to cell K3. 'Suggestions? DOUG |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting and Cell References
Bernie: It should do that, but it does not do that. All of the subsequent
references in the column are to the original cell and not to the subsequent cells in the same row, despite the fact I did not use an absolute reference in the original formula. It is very frustrating, because I have to fix about 100 cells throughout the workbook. I have tried CopyPaste SpecialFormulas and dragging the little black cross down the column to duplicate the same formula in subsequent rows. Neither method worked as advertised. (This was not a problem in Office 2003, as far as I know). DOUG "Bernie Deitrick" wrote: Doug, I think we may be talking at cross purposes. Are you talking about formulas in the cell, or formulas in the Conditional Formatting? The CF cell references will automatically update to the correct row if the CF cell reference is =K2 and not =$K$2, or =K$2 (a fourth possibility is =$K2, but that would work for additional rows within the column) when you copy and paste formats (or paste all)... HTH, Bernie MS Excel MVP "DOUG ECKERT" wrote in message ... Bernie: I can change the cell references one-by-one, but that is too labor intensive. Is there not a way to adjust the formula automatically? I thought Paste SpecialFormulas would do so, but it maintains the original cell reference in the conditional formatting formula, without any adjustments as to the row. DOUG "Bernie Deitrick" wrote: Doug, Select N2, then use Format / Conditional Formatting Cell Value is Greater than or equal to =K2 HTH, Bernie MS Excel MVP "DOUG ECKERT" wrote in message ... I've worked out how to format a row based on one cell in the row but when I try to copy that formula to my entire column every row's color is conditional to the value of the first cell, not to the cell for each respective row. (Same question as SlyGuy2183, but I could not see where he had received a satsifactory answer. My question is the same). I have no absolute references, i.e., no "$"s in my formula). Formula is "cell value (in N3) =K2, color it blue". When I extend that conditional formula to cell N3, it still refers to cell K2 instead of to cell K3. 'Suggestions? DOUG |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting and Cell References
Doug,
Are you using Excel 2007? Try a rule with the formula =N2=K2 HTH, Bernie MS Excel MVP "DOUG ECKERT" wrote in message ... Bernie: It should do that, but it does not do that. All of the subsequent references in the column are to the original cell and not to the subsequent cells in the same row, despite the fact I did not use an absolute reference in the original formula. It is very frustrating, because I have to fix about 100 cells throughout the workbook. I have tried CopyPaste SpecialFormulas and dragging the little black cross down the column to duplicate the same formula in subsequent rows. Neither method worked as advertised. (This was not a problem in Office 2003, as far as I know). DOUG "Bernie Deitrick" wrote: Doug, I think we may be talking at cross purposes. Are you talking about formulas in the cell, or formulas in the Conditional Formatting? The CF cell references will automatically update to the correct row if the CF cell reference is =K2 and not =$K$2, or =K$2 (a fourth possibility is =$K2, but that would work for additional rows within the column) when you copy and paste formats (or paste all)... HTH, Bernie MS Excel MVP "DOUG ECKERT" wrote in message ... Bernie: I can change the cell references one-by-one, but that is too labor intensive. Is there not a way to adjust the formula automatically? I thought Paste SpecialFormulas would do so, but it maintains the original cell reference in the conditional formatting formula, without any adjustments as to the row. DOUG "Bernie Deitrick" wrote: Doug, Select N2, then use Format / Conditional Formatting Cell Value is Greater than or equal to =K2 HTH, Bernie MS Excel MVP "DOUG ECKERT" wrote in message ... I've worked out how to format a row based on one cell in the row but when I try to copy that formula to my entire column every row's color is conditional to the value of the first cell, not to the cell for each respective row. (Same question as SlyGuy2183, but I could not see where he had received a satsifactory answer. My question is the same). I have no absolute references, i.e., no "$"s in my formula). Formula is "cell value (in N3) =K2, color it blue". When I extend that conditional formula to cell N3, it still refers to cell K2 instead of to cell K3. 'Suggestions? DOUG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting with absolute references | Excel Worksheet Functions | |||
copy conditional formatting with 'unique' cell references | Excel Discussion (Misc queries) | |||
How do I copy conditional formats so it changes cell references? | Excel Worksheet Functions | |||
Excel: how to formulate conditional cell references | Excel Worksheet Functions |