Copying Formatting without reference

[95saal]

Hey, 

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

Thanks 

[#2]

@95saal 

It is really hard to follow, please explain better

[#3]

@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)

[#4]

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 cells

[#5]

@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?

[#6]

@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

[#7]

@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.

[#8]

@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 

[#9]

@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 :) 

[#10]

@mtarler

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.  

[#11]

@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.