• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Relative Referencing in Conditional formatting

S

SG

Member
Hi Guys,


This is my first post in the forum with a very silly question for all experts but a problem for me.In Conditional formatting, i'm using icon sets of 3 arrows.say i'm applying it in A1 based on values B1 & C1.When i drag it to A2, it still points to $B$1 & $C$1,however it must pint to b2 & c2.Please help me .
 
SG


Firstly, Welcome to the Chandoo.org forums.


Use $B1 and $C1 in your formulas instead of $B$1 and $C$1


Excel adjusts the formulas for the second and other rows automagically
 
Hi SG,


Welcome to this forum.


Regarding your question, I do not understand the need for dragging the cells down to apply your conditional formating.


You just need to select the range of your data before you start applying the conditional formatting, so that your range will be automatically set at "applies to section" of conditional formatting dialogue box


Let me know if I fail to understand your requirement.


Regards,

Kaushik
 
Welcome!


Change the cell formula reference to be $B1 and $C1. Just need to remove the dollar signs from in front of the row references.
 
Hi Hui,


Thanks for the welcome.I really learnt alot from chandoo's forum.Really appreciate starting such forums.


Now,for the problem,When i write $B1 ..it says for icon sets, excel doesn't allow relative referencing.
 
SG

You can't use Icon sets with Formulas


Can you walk us through step by step what your doing?
 
Hi kaushik03,


I need to copy the format to all the cells in the range based on the values in the adjacent cells.
 
SG

Can you upload a sample file with a description of what/where you want something to apply


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
First i select the icon sets & choose Rule "Format all cells based on their values".say i have to apply it in cell A1 based on values in B1 & C1.Then in "value", i give "$B1" as reference & so on for next icon & in "Type" as "number".Then Excel gives the error for not allowing relative referncing.
 
Hi Hui,


PFA at belw path


https://hotfile.com/dl/163886849/7617182/Conditional_formatting.xlsx.html


& look for the cell c2 & c3 formatting which doesn't go with their adjacent cells
 
What Formulas and Values are you putting in the Value and Type Boxes in CF Edit?
 
SG


Even though the formulas still reference A2 and B2 the CF's work properly in C3 & C4 ?
 
ohh sorry....I mean to say CF in C3 & C4 still points to the values in A2 & B2 which should point to their adjacent cells.
 
Correct, But Change A3, A4, B3 or B4 and the C3 and C4 CF's change as they should!
 
One by one,it can be changed,my concern is this when i copy the format to no. of cells, they are not pointing to their adjacent cells.
 
SG

If the CF is doing the right thing what is the problem?


It looks like Excel keeps track of the Formula and the relativity of the current cells to the Formula internally and adjusts the Cf accordingly
 
I understand that

But the CF's are working

If I change A4, the Icon in C4 changes

Same with B4


I assume Excel keeps track of the relationship between the current cells and the Original Cells that it was applied to and then adjusts the CF internally
 
Hi SG ,


If you have gone through the link , you would see that icon sets have the problem you have pointed out ; the link shows what can be done.


Narayan
 
I am having the same issue as well. I would like to use arrow icons to indicate where values have gone from one month to the next. For example is the value went from 1 --> 1.5 I'd like to show and arrow pointing up. Because the reference is absolute, it will only work cell by cell and not allow to fill the formatting down without referencing the original cell. Seems silly Excel prevents you from adjusting the absolute reference to look at rows or columns.
 
You must log in or register to reply here.
Back
Top