Formatting a cell based on multiple parameters

lhunt0194

New Member
Joined
Aug 28, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am looking to have a cell essentially put a in checkmark and green fill if a few conditions are met. It's a bit complicated so I will explain as best I can;

The cell I want the formula in is U:9
The same sheet has 'important cells' B:9, I:9 and T:9
I have a list on a separate sheet (but in the same document) which has 3 columns (A, B, C) and 12 rows (1 - 12, 1st row are headers, rest are numerical values)

the set-up is:
- B:9 Will have one of the column headings from the separate sheet list (B:1 or C:1) manually put in
- I:9 Will have a numerical value from the 'A' column list (A:2 - A:12) manually put in
- T:9 Will have a calculated value in it from other cells

So for example lets say B:9 has 'heading1', I:9 has 150, and T:9 shows 0.50.
B:9 determines which column to check ('heading 1' is for column B), I:9 determines which row down on 'A' column (for example lets say 150 is on A:5, so it's looking for the value on B:5)
Then U:9 takes that value and compares it to the T:9 value ( in this example 0.50). If the value is greater than 0.5 it is 'wrong' (U:9 red fill), if it is less than 0.50 it is 'right' (U:9 green fill)

Hopefully that explains it clearly enough.

Regards,
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
If the essence of your explanation is, if U9 are above, or below 0.50, then just use conditional formatting in U9. It's easy set up!
 

lhunt0194

New Member
Joined
Aug 28, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
If the essence of your explanation is, if U9 are above, or below 0.50, then just use conditional formatting in U9. It's easy set up!
That’s the ultimate goal, but it’s getting the value for U9 first to compare it. 0.5 was just an example.
 

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
That’s the ultimate goal, but it’s getting the value for U9 first to compare it. 0.5 was just an example.
The amount are in reality not important; you can choose the Top and Bottom of the value. But set it up in Conditional Formatting. You can do it, without formula, in CF.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,536
Messages
5,548,626
Members
410,859
Latest member
RamIndia
Top