SOLVED

Excel formula

Occasional Contributor

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 change to 8 AM automatically and If I change the value of cell 1 to 8 AM then the cell 2 value should change to 6 PM automatically and vice versa for Cell 2 as well

 

Note- the values in cells will either be 6 PM or 8 AM

 

Currently, I've used data validation to create a drop-down list where I can select either be 6 PM or 8 AM manually for both cells

 

Time (Cell 1)6 PM (Cell 2)8 AM

 

 

Query 2 - 

If I select Night from C0's drop-down list then the value of C1 and C2 should automatically change to 6 PM and 8 AM respectively and If Day is selected from C0's drop-down list then the value of C1 and C2 should automatically change to 8 AM and 6 PM respectively

 

(C0)Night /DayHours (EST)(C1) 6:00 PM(C2)8:00 AM

 

Note - If we can resolve Query 2 then no need for resolving Query 1.

 

Regards,

Arbaz Q

16 Replies
best response confirmed by AQureshi1709 (Occasional Contributor)
Solution

@AQureshi1709 See attached file for solutions to both queries.

@Riny_van_Eekelen Thank you soo much!!!

The only issue I am facing is that my Data i.e Day, Night, 6 PM & 8 AM is on different sheet2 of the same file.

PFA, added sheet 2 on the same file.

I did it!!!!!!!!!!!! Thanks a bunch, Sir!!!!

@AQureshi1709 See attached. I chose to use named ranges for the two relevant areas. Then it doesn't matter where in the workbook they are. 

@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?

 

@Riny_van_Eekelen This is Great!!! Looks way easier than the last formula we used. But I am unable to implement on my original sheet

@AQureshi1709 Sorry, but I don't understand your last question.

@Riny_van_Eekelen Apologies!

Can you use the same formula on my original sheet?
AQureshi1709_0-1667072880409.png
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

 

AQureshi1709_1-1667076531660.png

PFA, original sheet.

 

@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?

@Riny_van_Eekelen 

First of all, really appreciate you taking the time to address these silly mistakes on this platform.

 

1) Thank you, Sir! I've recently started working with Excel for a few weeks.
I didn't pay attention to Named Ranges which you mentioned earlier before.
I did some research and was able to figure out what you actually meant by Named Ranges
I tried again and it worked.

 

2) Currently we do everything manually - Eg. changing Job status in column F and depending on that we change H6 color
If in column F all job status is green, we manually change H6 to Green via Dropdown. 

If in column F any one job status is Red/Failed others can be Green/Orange/Yellow, we change H6 to Red

 

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.


If possible can we get on a zoom call so that I can explain more clearly - I will be available from 3 - 7 PM EST.

 

 

@Riny_van_Eekelen Not sure what's going on.

 

I have this rule set from F13:F30, but it is only working for F13

If I keep F13 blank and change the status of F14 nothing happens to H6.

So, I created a Test rule only for F14 and that worked.

 

AQureshi1709_0-1667191624853.png

 

Also, sometimes if I change the status of F13 the Status of H6 does not change. so in that case I have to go to H6 and press Delete and then I can see the color as per status.

 

@AQureshi1709 I changed my previous file a bit, now that I understand that you want H6 to be coloured based on the "worst" status present in column F, where worst is Failed, then Dependency, then Running, and finally Completed.

 

You will see that H6 now has 4 CF rules checking the status fields in F in the above-mentioned order and stopping after each if it find one. 

Also changed the CF rules for Column F as the file I worked on earlier had many rules that didn't serve any purpose.

 

See if the attached file does what you need.

@Riny_van_Eekelen This is just soo perfecttttt!!!

=SUM(IFERROR(FIND("Failed",$F$13:$F$53),0))

Change you please give me a short summary of what's going on with the above formula?

 

Also, If we delete E6 - H9 and I9 changes to error #N/A - is there something we can do about it? Like simply keep it blank if E6 has no values in it?

AQureshi1709_2-1667258539349.png

Also, I had another query. This is resolved using this link - https://www.extendoffice.com/documents/excel/3691-excel-auto-numbering-insert-row.html

 

I have raised another issue which is related to Excel VBA and Outlook - If you have knowledge on that, I would really appreciate your assistance.

https://techcommunity.microsoft.com/t5/excel/excel-automation-via-vb-to-fetch-if-outlook-email-is-re...

Thanks a bunch, Sir!!

@AQureshi1709 

You need to dissect a formula lik =SUM(IFERROR(FIND("Failed",$F$13:$F$53),0)) to understand what's going on. Start from the inside out and enter three formulas side by side

 

=FIND("Failed",$F$13:$F$53) looks for the word Failed in range F13:F53 and returns an array of errors and 1's . The 1's indicating the position(s) in the array where the word was found.

 

Add the IFERROR bit. Thus will replace the errors in the array with 0's .

=IFERROR(FIND("Failed",$F$13:$F$53),0) 

 

Finally, SUM that last array. If the sum = 0, it means that "Failed" was not found. Anything else indicates that it was found and you can use that to conditionally format the cell.

 

To avoid error messages when E6 is blank, wrap the formulas that produce the error in IFERROR, similar to what I described above. For instance =IFERROR(your_formula,"") to return an empty string is your_formula returns an error.

 

VBA is not my kind of thing. I stay away from it as much as I can.

@Riny_van_Eekelen 

 

I tried IfError on both H and I columns.
It seems to returns 8:00 AM when E6 is blank, works fine for H column.

AQureshi1709_0-1667444849918.png

 

@AQureshi1709 Sorry about that. Change the formula to:

=IF(H17="","",IF(H17=TIME(18,0,0),TIME(8,0,0),TIME(18,0,0)))