Hi,
I am setting up a Balanced Scorecard in Excel 2016 and wondered if I could automate the process as I have a lot of data to go through. I was thinking if doing this by conditional formatting but do not know if this is possible?
I want to put the Balanced Scorecard in Sheet1 and in Sheet2 I have all the data.
As an example this is the Balanced Scorecard section populated for a small section. The green cells are B2 and C2. The two red cells next to each other B3 and C3. The Orange cell is B4.
And here is the data to populate the above.
For Revenue 1 (the Green cells I want) The March 100 is in Cell F3, The March Budget is in Cell G3. The Q1 Actual Cell is in H3. The Q1 Budget Cell is in I3.
For Revenue 2 (the Red cells I want) The March 80 is in Cell F4, The March Budget is in Cell G4. The Q1 Actual Cell is in H4. The Q1 Budget Cell is in I4.
For Revenue 3 (the Orange cells I want) The March 70 is in Cell F5, The March Budget is in Cell G5. The Q1 Actual Cell is in H5. The Q1 Budget Cell is in I5.
So taking March and Revenue 3 as an example. I want the formula/conditional formatting in sheet 1 (Cell B4) to look at sheet 2 and say if F5 is greater than G5 then highlight cell Green. If cell F5 is less than G5 but greater than D5 then highlight cell Orange. If Cell F5 is less than G5 and less than D5 then highlight cell Red
I am setting up a Balanced Scorecard in Excel 2016 and wondered if I could automate the process as I have a lot of data to go through. I was thinking if doing this by conditional formatting but do not know if this is possible?
I want to put the Balanced Scorecard in Sheet1 and in Sheet2 I have all the data.
As an example this is the Balanced Scorecard section populated for a small section. The green cells are B2 and C2. The two red cells next to each other B3 and C3. The Orange cell is B4.
And here is the data to populate the above.
For Revenue 1 (the Green cells I want) The March 100 is in Cell F3, The March Budget is in Cell G3. The Q1 Actual Cell is in H3. The Q1 Budget Cell is in I3.
For Revenue 2 (the Red cells I want) The March 80 is in Cell F4, The March Budget is in Cell G4. The Q1 Actual Cell is in H4. The Q1 Budget Cell is in I4.
For Revenue 3 (the Orange cells I want) The March 70 is in Cell F5, The March Budget is in Cell G5. The Q1 Actual Cell is in H5. The Q1 Budget Cell is in I5.
So taking March and Revenue 3 as an example. I want the formula/conditional formatting in sheet 1 (Cell B4) to look at sheet 2 and say if F5 is greater than G5 then highlight cell Green. If cell F5 is less than G5 but greater than D5 then highlight cell Orange. If Cell F5 is less than G5 and less than D5 then highlight cell Red
