Forum Discussion
Copying Formatting without reference
I know its not clear as I am struggling on how to explain it.
The reference for me is not the issue, cause i need every cell to be its own reference as shown in the picture. My main issue iwant to copy the formatting from the original cell to all the other cells while each cells formatting is only affecting it self. I don't know if thats clear but let me explain what i am trying to do.
So, I am creating a tracker the top row to show how many pieces of product is repaired, and the row below it is the date for the latest update.
The first condition i have is on the bottom row is that if the date has not changed in 3 days the color will change for example A1 is how many pieces and A2 is the date i used this fourmla
( =A2-TODAY()<=-3 )
Also, the condition is if the too row has the text done in it to change the fill color to green, so even after 3 days it will stay green, the fourmla for this one is this:
(=A1="DONE")
As you can see these fourmlas both have to have the same cells that they are applied to be the reference.
My issue is i have 20000 cell in this tracker i want to apply this formatting for each one individually but the only way i can find is by the format printer, but when i pull on all cells it applies the rule on all cells before starting with the top left cell.
I attached a picture showing what happens when I use the format printer on all the cells at once and how it just applies to all the cells before it, and in the other picture if copy the formatting and paste on each cell individually and it works, but i can not do that with 20000 cells
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?
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.