Forum Discussion
Copying Formatting without reference
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:
a) =INDIRECT("A2")
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)
- Thanks for your reply.
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 cells95saal 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
