Aug 14 2019 07:49 AM
Hello,
I have multiple worksheets in Excel 365 that present data from one month to another. I am trying to make the data turn green if it's gone up since the previous month or red if it's gone down. I would like to know the quickest way to copy-paste the conditional formatting so it is relative to the previous month and not reference the original cell in the formula. ex: I have sales in column A2 to A50 in worksheet february and would like to format it n relation to column A2 to A50 in worksheet january while matching A2 feb with A2 jan, A3 feb with A3 jan... I hope I'm being clear. If it's easier with Icons, I can do that instead. Thanks!
Aug 15 2019 06:46 AM - edited Aug 15 2019 06:48 AM
For February, you could use conditional formats with these formulas:
(RED)
=A2<January!A2
(GREEN)
=A2>January!A2
Make sure to apply the conditional formats to your necessary ranges.
Then for each month that follows just simply change "January" to the previous month.
Aug 15 2019 07:39 AM
Hi @PReagan
That works but how do I copy paste it to the rest of my cells without it comparing to A2 but to the following cells automatically. If I want to compare A2 (january) with A2 (february), A3 (january) with A3 (february) and so on?
Thanks! :)
Aug 15 2019 07:45 AM
Aug 15 2019 07:56 AM
You may add in some cell of the each sheet the name of the previous sheet, let say in Feb!C1 it'll be "Jan", in Mar!C1 - "Feb", etc.
After that you may apply conditional formatting rule in Feb sheet with the formula
=A1>INDIRECT($C$1&"!"&ADDRESS(ROW(),1,4))
(I started range from A1, otherwise adjust ROW() part)
By Format Painter you may copy this formatting on next sheets (Mar, Apr, etc)
Aug 15 2019 09:14 AM
Ok, so I think there is something I'm not understanding or it's just not working for me... Here is my worksheet... maybe that would help? Thanks again for your help guys! I really appreciate it! :)
So, I need to know if my score has increased or decreased since the previous month. All months have the same set-up... I've also attached it.
Aug 15 2019 09:35 AM
Please check attached. I created rule for Feb, added March sheet and apply same formatting to it by Format Painter. Sheet name is in F1.
Aug 15 2019 09:43 AM
Aug 15 2019 09:52 AM
@melissach , glad to help, have a nice day too!