Forum Discussion
Copying Formatting without reference
mtarler Thanks, that solved the issue finally. the only question is that I'm having some hard time trying to understand the formula.
Do you mind explaining it?
Thanks
95saal glad it is working for you and sure I can explain it. I'm guessing you know some of much of it but since you didn't specify what part of the formula I will try to go over all of it but if there is a particular part let me know:
=AND(ISNUMBER(F1),TODAY()>=F1+3,ISODD(ROW(F1)))
so it is checking if 3 conditions are all true (AND):
ISNUMBER(F1) checks if the cell is a number (i.e. not text)
TODAY()>=F1+3 checks if TODAY() is at least 3 days after the value in the cell
ISODD(ROW(F1)) checks if the cell is in an odd numbered row
Now the 'tricky' part is that it all uses the cell 'F1' in the formula. So in excel there are relative references and absolute references:
So lets say in G1 we enter the formula =F1. Then I copy or drag G1 down to G2. Then in G2 it will have the formula =F2. If I copy it from G1 and G2 to H1 and H2 then the formulas in H1 & H2 will be =G1 and =G2 respectively. (You might need to actually do this to see what I mean). So that is RELATIVE reference because in G1 we entered =F1 but what Excel sees is 'look at the cell 1 column to the left'. So when you copy or drag (using the little dot/plus in the bottom right of the cell when highlighted) or fill down/right then Excel will adjust the reference to always point to 'the cell 1 column to the left'. NOTE: if you cut and paste or literally drag the cell itself to a new location it will NOT change the reference (i.e. the formula will no longer be present in the previous cell).
Now lets say in G1 we entered the formula =$F$1 instead. Then it doesn't matter what you do in terms of copying, dragging, filling etc... the new cell will always have the formula =$F$1. That is because this is and ABSOLUTE reference.
Lastly you can also have hybrid like $F1 or F$1. So what ever has the $ in front of it is absolute and what doesn't is relative. So F$1 will always point at row 1 but the column can change. If you copy down it will always point at F$1; if you copy to the right then it will point to G$1, H$1, etc...
So finally back to the original conditional formatting formula that all referenced 'F1'. The conditional formatting uses the upper left corner of the area it is applied to as the starting/reference point. Any relative references are based on that upper left corner cell. So after it finished with that 1st cell (in this case F1) it moves to the next cell and updates the relative reference accordingly. So by using the upper left cell in the formula it tell Excel to apply that formula to every cell in the 'applied to' range based on the value in its own cell.
I do hope that explanation helps and please feel free to like my post(s) and mark as best response if so.
Best of luck 🙂
95saal so the formula is checking for 3 conditions to exist:
ISNUMBER(F1), this makes sure the cell has a number in it (i.e. not the header that says "Stage 1")
TODAY()>=F1+3, this is the actual condition you wanted to highlight
ISODD(ROW(F1))), this makes sure it is a row that has a date as opposed to the quantity. So rows 2, 4, 6, etc... have a quantity that you don't want to highlight and rows 3, 5, 7, etc... are the dates you do want to check and highlight. I used ISODD because it was easy but if your dates were every fourth or fifth row then I would use something like MOD(ROW(), x)=0. If you don't add this condition then every one of your quantity rows would get highlighted. Basically a date is nothing more than a number as in number of days since a starting date (where 1 is 1/1/1900). so today is some number >40,000 and will definitely be > than your quantity values + 3. An alternative condition would be to check if that cell is > some value (e.g. >44,000) but who knows, maybe your quantities will grow in the future so I opted to rely on the format of the sheet having the dates in the odd columns.
I hope that helps explain it better.
Thanks for the explanation, I probably should have been more clear with my question.
so, I understand what each formula does on its own, what I can't really understand is how do they work together. like for example, why do I need an ISODD function there?. I tried to play around with it and it works well but I need to understand how does work as a whole function, like why don't I need just the AND and Today formula together. I am sorry its just confusing me.