Use formula to change cell value dependant on another

[sharky12345]

I've got this formula which updates a cell dependant on another - it shows either true or false dependant on the other value;

=IF(OR(LEFT(J26,5)="Leave",LEFT(J26,4)="Sick",LEFT(J26,4)="Rest Day"),FALSE,OR(,LEFT(J26,1)="E",LEFT(J26,1)="D",LEFT(J26,1)="L",LEFT(J26,1)="N"))

What I'm trying to do is add a condition that will show 'true' if J26 is 'NTE' but I can't get it to work.

The formula was on a worksheet sent to me, so not something I have created myself and if I'm honest I don't fully understand it, so any help that can be given would be appreciated.

 

[SyedUsman]

=IF(J26="NTE",TRUE,IF(OR(LEFT(J26,5)="Leave",LEFT(J26,4)="Sick",LEFT(J26,4)="Rest Day"),FALSE,OR(,LEFT(J26,1)="E",LEFT(J26,1)="D",LEFT(J26,1)="L",LEFT(J26,1)="N")))

 

[Peter_SSs]

I've got this formula which updates a cell dependant on another - it shows either true or false dependant on the other value;

=IF(OR(LEFT(J26,5)="Leave",LEFT(J26,4)="Sick",LEFT(J26,4)="Rest Day"),FALSE,OR(,LEFT(J26,1)="E",LEFT(J26,1)="D",LEFT(J26,1)="L",LEFT(J26,1)="N"))

What I'm trying to do is add a condition that will show 'true' if J26 is 'NTE' but I can't get it to work.

The formula was on a worksheet sent to me, so not something I have created myself and if I'm honest I don't fully understand it, so any help that can be given would be appreciated.

Note that the red part will never be true. Did you mean 8 instead of 4?

 

[sharky12345]

Hadn't spotted that Peter so thank you, and thanks to Syed too!!

 

[Peter_SSs]

What I'm trying to do is add a condition that will show 'true' if J26 is 'NTE' but I can't get it to work.

I don't think you need any change at all to your original formula for that.
If J26 is 'NTE' it would already meet the existing condition in your formula LEFT(J26,1)="N" which would cause that final OR() function to return TRUE.

Are you sure that it didn't work without change?

As an aside, you can shorten that second OR() function as shown in red below and, depending on what text may appear in J26, you may be able to shorten the first one as shown in blue.

=IF(OR(LEFT(J26,4)={"Leav","Sick","Rest"}),FALSE,OR(LEFT(J26,1)={"E","D","L","N"}))

 

[sharky12345]

Perfect, I'll give that a try. It certainly wasn't working before I made the change but it is now....thank you Peter!

 

[Peter_SSs]

It certainly wasn't working before I made the change ....

Hmm, must have been something else going on. Here's my sheet with the formula exactly as in post 1 and it is returning True for 'NTE' as I would expect.

Excel Workbook
	J	K
26	NTE	TRUE
True False