Forum Discussion

AQureshi1709's avatar
AQureshi1709
Copper Contributor
Oct 23, 2022
Solved

Excel formula

Hi All,   Query 1- I need a formula where if I change the value in a cell the value of the other cell should change Eg - If I change the value of cell 1 to 6 PM then the cell 2 value should chang...
excel
Formulas and Functions
AQureshi1709's avatar
AQureshi1709
Copper Contributor
Oct 28, 2022

Riny_van_Eekelen  I have another Query now! I am sharing the original sheet which I am working on.

This time I need assistance working with Job Status and Status-

If you see I have the Job Status List as Completed, Failed, Dependency, and Running along with the color assigned to each of them

What I usually do is manually change the Status color as per the Job-status list.

 

if there is any 1 Red job (Rest can be Yellow/Green/Orange) in the list then automatically mark the Status as Red.

if there is any 1 Orange job (Rest can be Yellow/Green/No Red) in the list then automatically mark the Status as Orange.

if there is any 1 Yellow job (Rest can be No Red/Green/ No Orange) in the list then automatically mark the Status as Yellow.

if there is any 1 Green job (Rest can be No Red/No Yellow/ No Orange) in the list then automatically mark the Status as Green.

 

Priority - Red, Orange, Yellow, Green

 

Do we have any formula for this?

 

    • AQureshi1709's avatar
      AQureshi1709
      Copper Contributor
      Oct 29, 2022

      Riny_van_Eekelen Apologies!

      Can you use the same formula on my original sheet?

      I tried but I am getting an Invalid Name error!

      =INDEX(times,MATCH(E6,End_of_Business_Day End_of_Overnight,0))

      =INDEX(times,MATCH(E6,End_of_OvernightEnd_of_Business_Day,0))

      =INDEX(times,MATCH(E6,Data!C2:C3,0))

       

      Could you please assist me with what I am doing wrong? or where the actual mistake is?


      Query 2 - If you see the below screenshot where I have highlighted the Job Status column and Status
      -So the thing is if the job status is Running we mark the Status with the color Yellow, Failed/Red, Completed/Green, and Dependency/Orange manually as shown in the screenshot

      -I am looking for a formula where I can automate the process

      Eg- If I have a Job that is Failed/Red then the Status color should automatically change to red without me doing it manually

       

      PFA, original sheet.

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Oct 30, 2022

        AQureshi1709 

        Okay!

        On 1) you didn't set up the named ranges as I suggested. Have done that now.

         

        On 2) you want the color of cell H6 to change automatically, based on the Job Status in Column F. In your example, the latter has all four of the possible status filled in. What would trigger the color for H6?

Resources