Forum Discussion
Copying Formatting without reference
mtarler Ok let me explain again.
As you see in the uploaded document, I am developing a tracker.
I have a certain number of parts in each job up to 160, and I have a number of stages that my parts need to go through.
So, As soon as a I get to any stage of my process I will put he date in the second cell for example in F2 I will use the drop down menu to chose the amount of parts that reached the first stage or if its done, and right below it in F3 I will use the drop down menu to use the today formula. What I am trying to do is for example if in F2 I put 50 and I put the date in F3 as 10/11/2020, what I need is some conditional formatting that it will turn red if that date passes three days and if it is done it will turn green regardless of how many days. I Managed to put a formula that will do that but I have to do individually for each cell and in my tracker I am planning on having over 100000 cells so doing each of them individually might be a bit hard, and if I just apply the same rule to all the cells from the first one the formatting will consider the first cell the reference to check if the date passed three days or not.
maybe there is another way to go other than this way maybe using IF formula but I'm desperate as I have been trying to figure this out for a while. I hope it is easier to understand now
95saal I looked at your sample sheet and it looks really close. You can reference the entire columns F:M instead of individual rows and then add a condition based on the row, in your case it would simply be isodd(row(F1)). So it can/should look like this:
=AND(ISNUMBER(F1),TODAY()>=F1+3,ISODD(ROW(F1)))
Alternatively or in addition, you can check that the value is > 40,000 which would be well above your quantity levels (based on your drop-down) and represents a date in 2009 (see attached sheet). Or you can use a number like 44,000 since that is the middle of 2020.
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 🙂
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.