How to change value in a cell based on multiple conditions

**rizwanulhasan** · 01-12-2022, 01:38 PM

Hi All,

In the attached excel file, Column C changes its status to Not Available if the value in Column D is Do Not Disturb and also based on other few criteria from another sheet.

I have added a column E with drop down as Out Of Office.

I want to retain the existing formula and also want to add an additional formula to the existing one such that if Column E contains Out Of Office, then the status in column C should be Not Available

In other words, if any one column either D or E are selected with the option from drop down, then the status in column C should be Not Available.

Note: Existing formula needs to be retained.

Can someone help with both the formulae in a single cell?

Thanks in advance.

**TMS** · 01-12-2022, 01:51 PM

Try:

Formula:

=IF(OR(D4="Do Not Disturb", E4="Out of Office"),"Not Available","")

**rizwanulhasan** · 01-12-2022, 01:59 PM

Hi TMS,

Thank you for your prompt response.

However, as mentioned earlier, there is already a formula existing in column C. I would like to retain this existing formula and add the new condition to the same formula.

Can you help me with the combination of both formulae.

Thank you.

**TMS** · 01-12-2022, 03:18 PM

I'm looking at this on an iPad and all I can see in column C is #REF! with a message:

This cell has an external reference that can't be shown or edited. Editing this cell will remove the external reference.

**TMS** · 01-12-2022, 04:19 PM

You ALREADY have the OR! It's just another condition.

Formula:

=IF(OR(D4="Do Not Disturb",  E4="Out of Office", 

               COUNTIFS('C:\Users\rizwa\Desktop\Serialisation - Tickets & DM Database (Test).xlsm'!Table5[MUD ID2],'DM PR Availability'!A4,

                                     'C:\Users\rizwa\Desktop\Serialisation - Tickets & DM Database (Test).xlsm'!Table5[Status],"Pending")>0,

               COUNTIFS('C:\Users\rizwa\Desktop\Serialisation - Tickets & DM Database (Test).xlsm'!Table5[MUD ID2],'DM PR Availability'!A4,

                                     'C:\Users\rizwa\Desktop\Serialisation - Tickets & DM Database (Test).xlsm'!Table5[Test Column],TODAY(),

                                     'C:\Users\rizwa\Desktop\Serialisation - Tickets & DM Database (Test).xlsm'!Table5[Status],"Completed")>1),

"Not Available","Available")

**rizwanulhasan** · 01-12-2022, 11:52 PM

Oh this was it !!

I was trying to enter whole formula again and it wasn't working.

Thank you so much TMS.

**TMS** · 01-13-2022, 03:01 AM

You're welcome. Thanks for the rep.

How to change value in a cell based on multiple conditions

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to change value in a cell based on multiple conditions

Re: How to change value in a cell based on multiple conditions

Re: How to change value in a cell based on multiple conditions

Re: How to change value in a cell based on multiple conditions

Re: How to change value in a cell based on multiple conditions

Re: How to change value in a cell based on multiple conditions

Re: How to change value in a cell based on multiple conditions

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Change a cell's font color based on conditions

Conditional formatting - based on multiple conditions which can change

Change Cell value format in all sheets based on multi conditions

[SOLVED] Change pattern based on cell value (Multiple conditions)

[SOLVED] Set Cell Value Based on Multiple Conditions

How to Change Absolute Cell Reference based on a couple of conditions

Cell value based on multiple conditions

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions