Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-02-2020, 11:08 AM
Oldman45 Oldman45 is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2016
Novice
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor
 
Join Date: Jul 2020
Posts: 7
Oldman45 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 07-02-2020, 01:35 PM
Purfleet Purfleet is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #3  
Old 07-03-2020, 01:55 AM
Oldman45 Oldman45 is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2016
Novice
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor
 
Join Date: Jul 2020
Posts: 7
Oldman45 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 07-03-2020, 02:01 AM
Purfleet Purfleet is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #5  
Old 07-03-2020, 02:49 AM
Oldman45 Oldman45 is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2016
Novice
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor
 
Join Date: Jul 2020
Posts: 7
Oldman45 is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 07-03-2020, 03:08 AM
Purfleet Purfleet is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

can you attached an example work sheet?


You shou be able to highlight the range and just put the formula in the first cell
Reply With Quote
  #7  
Old 07-03-2020, 03:27 AM
Oldman45 Oldman45 is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2016
Novice
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor
 
Join Date: Jul 2020
Posts: 7
Oldman45 is on a distinguished road
Default

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
Attached Files
File Type: xlsx Draft Organsational_Accident_Trend.xlsx (58.9 KB, 7 views)
Reply With Quote
  #8  
Old 07-03-2020, 11:25 AM
Purfleet Purfleet is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Attached Files
File Type: xlsx Draft Organsational_Accident_Trend_purfleet.xlsx (56.5 KB, 6 views)

Last edited by Purfleet; 07-03-2020 at 11:59 PM. Reason: typo
Reply With Quote
  #9  
Old 07-03-2020, 11:57 PM
Oldman45 Oldman45 is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2016
Novice
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor
 
Join Date: Jul 2020
Posts: 7
Oldman45 is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 07-04-2020, 12:42 AM
Oldman45 Oldman45 is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2016
Novice
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor
 
Join Date: Jul 2020
Posts: 7
Oldman45 is on a distinguished road
Default

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.
Reply With Quote
  #11  
Old 07-04-2020, 01:10 AM
Purfleet Purfleet is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #12  
Old 07-05-2020, 12:59 AM
Oldman45 Oldman45 is offline How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Windows 10 How to compare two cells in a worksheet and show the result as red or green in a cell in another wor Office 2016
Novice
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor
 
Join Date: Jul 2020
Posts: 7
Oldman45 is on a distinguished road
Default

Good Morning Purfleet

Thank you for that and all your help.
Reply With Quote
Reply

Thread Tools
Display Modes


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
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor One Cell that controlls spread sheet result button to change simple fomula result RAH Excel Programming 5 03-31-2018 04:52 PM
How to compare two cells in a worksheet and show the result as red or green in a cell in another wor 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft