Aug 24 2020 04:28 AM
Aug 24 2020 04:28 AM
I am not sure how to explain my issue but i attached pictures that show the problem that i am facing.
So, i have some conditional formatting on some of my cells. I want to copy that formatting while every cell formatting affect the same cell it self. The issue is that some time the original cell is still the reference, and when I solved that the issue when I try to copy the formatting to all the other cells it the condition for each cell affects all the cells before it in line. So, my only solution is to copy the formatting and only pasting it to one cell, which is not convenient since I am trying to apply it to a table of more than 20000 cells. I hope that was clear.
The attached pictures should show an example of what I mean
Aug 24 2020 05:27 AM - edited Aug 24 2020 05:37 AM
@95saal as @Ramiz_Assaf mentioned it is still hard to understand what you are saying. But from the images it appears you have conditional formatting specifically applied to A2 and other conditional formatting applied to A1:B2. So as you copy A1:B2 across the row you would expect the formatting applied to entire copy area to be applied to the entire paste area and the formatting applied to only the bottom left to get applied to every other cell just like you pasted 4 cells, moved over pasted another 4 cells and so on across the row.
Just a thought... IF you wanted all the formatting to always be based on cell A2 after all the copy-paste then you need to use custom formula under the conditional formatting. If you are and the reference inside that custom formula is changing then you can use:
b) Define a name (click A2 and click Formulas->Define Name and call is something like CellA2.) Then in the conditional formatting you can use = CellA2
But you were highlighting the RANGE that custom formatting was being applied to so that makes no sense to always refer to A2 (formatting that cell once is enough)
Aug 24 2020 10:53 PM
Aug 25 2020 04:13 AM
@95saal maybe i still don't understand. did you try it and it doesn't give the expected results? conditional formatting can be a little hard to understand but the custom formula is applied as a relative reference based on the upper left most cell in the 'applied to' range. so if the formula says = A1="DONE" and the applied to range is A2:Z2 then every cell will look at the cell 1 row above it so Z2 will have conditional formatting applied if Z1="DONE" even though the formula says A1. Does that help?
Oct 11 2020 12:18 AM
@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
Oct 11 2020 07:13 AM
@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:
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.
Oct 14 2020 06:49 AM
@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:
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
Oct 18 2020 04:09 AM
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.
Oct 18 2020 01:47 PM
@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.