|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor
I have a trend graph measured in month periods that shows a trend line for a specific entity compared to a trend line for the average of all entities. I want to summarise that comparison in a separate worksheet listing all entities and showing for each month whether each entity's trend is above or below the average for all entities. Can anyone advise the best way to achieve this result?
In simple logic I want to determine: IF cell A is < than cell B then turn cell C (in another worksheet) green, IF NOT then turn cell C red. Thank you in advance |
#2
|
|||
|
|||
Conditional formatting should work for you.
Conditional formatting can be a bit moody to get use to the quirks but seems to do what you want See attached |
#3
|
|||
|
|||
Good Morning Purfleet
Thank you for your prompt response. Yes that certainly seems to do what I want - I did not realise it could be so simple! I have a subsidiary question if I may: My worksheet is set up in rows (not the tabular columns Excel seems to prefer) as this facilitated the trend chart I have created. Now the conditional formatting rules fix locations with $ so I cannot simply copy along the row but it appears I have to enter the rules for each cell. Is this correct or is there a way to copy and paste to save all that manual inputting? Thanks |
#4
|
|||
|
|||
There is a nak to conditional formatting Highlight the cells you want to change colour on and with the first cell selected do your formula. Before press enter or Oking the box click in the formula and remove the $.
it can be a pain and there are other ways but this does work with a bit of patience |
#5
|
|||
|
|||
Thanks again.
That does work but only if I copy one cell at a time and I have 25 rows that are 36 columns wide (900 cells) and will grow each month by a column! Also it strangely will not copy down a column, even one cell at a time. Is there any way to copy the first cell across a row? Thanks for your patience and help. |
#6
|
|||
|
|||
can you attached an example work sheet?
You shou be able to highlight the range and just put the formula in the first cell |
#7
|
|||
|
|||
Thanks - not sure how you do that but here is a copy of the workbook. I am not sure how the upload works but if I click the carat next to the paperclip above the document appears to be there.
The data input being used is on the first tab 'data input' and I am trying to summarise that on the 'Sheet 2' tab. On sheet 2 at cell D5 was my first rule set up that worked. Cell D6 was the next attempt being manually inputted and cells E6:J6 have been successfully pasted by copying D6 and then individually pasting each additional cell along row 6. Oh I have spotted from the preview that the document is there. Thanks |
#8
|
|||
|
|||
Sorry had to do work.....
To make things a bit more simple just do a normal if statement on the results page, then do a conditional format on that (see attached) Just as i side note, i would change the layout of your data, it might take you a while but it will save you from a whole world of pain later on - the school would be at the top and the date down the side Obviously its your call but it will make getting data out and displaying it much much easier and more useful fyi i changed the number in p68 to get a red cell on the sheet1 Last edited by Purfleet; 07-03-2020 at 11:59 PM. Reason: typo |
#9
|
|||
|
|||
Good Morning Purfleet
Thanks for your continuing help I really do appreciate it. I'll study your changes and let you know how I get on. |
#10
|
|||
|
|||
It is me again!
That is really useful and so simple - thank you. I appreciate I have taken up a lot of your time but wonder do you know of good tutorials for learning Boolean logic and formulae and conditional formatting too? I have used YouTube and such but find these sources somewhat limited. I came from the old school (Lotus 123, SuperCalc, and such) so find it easier to create charts and manipulate data in the 'non-tabular' form, as I find Excel's 'automated' approach difficult to understand and, due to my limited knowledge, it restricts my capability. However, I will reorganise this data as you advise and see if I can get to grips with Excel. Thanks again for all your help and patience. |
#11
|
|||
|
|||
If you want structured learning you cant go far wrong with Mynda, very good teacher https://www.myonlinetraininghub.com/
If you are more of a dipper (?) then YouTube Excel is fun is a very good channel and there is nothing he cant do with formulas |
#12
|
|||
|
|||
Good Morning Purfleet
Thank you for that and all your help. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy colored cells to new worksheet (maintain cell position) | naeemakhtar | Excel Programming | 2 | 06-06-2020 04:02 PM |
Data Compare and result as true or false in result sheet | aligahk06 | Excel | 1 | 08-29-2019 06:44 AM |
One Cell that controlls spread sheet result button to change simple fomula result | RAH | Excel Programming | 5 | 03-31-2018 04:52 PM |
Show result of formula as a value in another cell | Steve_D | Excel | 4 | 10-12-2014 07:38 PM |
Can I create a formula that will show result in the same cell and let me copy it down | CranstC | Excel | 1 | 02-11-2012 01:29 AM |