IF AND ELSE OR Statement Help requested

[TresRing]

Hi, I'm sure this will be an easy one for you all, but I can't find an answer online and I can't figure out how to make it work. I've tried various conditional formatting rules, which I'd prefer if possible, but none of them works when conditions are met. So I resorted to macros and have spent 2 days manipulating different codes with no joy. Can you please help me?

The scenario is, if Person1-Person5, and Person7 are traveling, their cost is Number1. If Person6 is traveling, their cost is Number 2. If any person1-7 is NOT traveling, their cost is 0 or blank.

This is the formula I need to work out for each sheet an entire 13-sheet workbook:

If H:H = Yes, Then J:J = Number1 (any yes on that column gets a number assigned to the adjacent)

Else If H:H = Yes, AND A:A = Name, Then J:J = Number2 (this is the exception)

Or If H:H = No, Then J:J = Blank
​

If you can help me get this to work either with CF or macros, I would be thankful to the moon and back.
/TR

 

[TresRing]

Is this an impossible task or something? It is for me, that's for sure. I can't figure it out. Does anyone have an idea about how to resolve this?

 

[Muhammad_Usman]

Welcome to board , Please clarify that you want to do it in conditional formatting or with simple data, and also share your sample data in tabular format for better understanding.

 

[TresRing]

Thank you for responding. I got pulled off this project and am just now getting back to it, so I apologize for not coming back to follow up until now. I do very much appreciate your help. Conditional Formatting may not be the answer, and I may need a macro to resolve this.

I'll re-ask the question with the table below in mind. This is the formula I need to work out for each sheet an entire 13-sheet workbook:

IF any cell in column D = Yes, then the adjacent cell in column E will be populated with a predesignated number

AND if any cell in column D = Yes, AND the cell in column B for that row = Exception Name, then the adjacent cell in column E will be populated with a different predesignated number

Or if any cell in column D = No, then the adjacent cell in column E = Blank
​

A	B	C	D	E	F
1	Name	Hours	(Y/N)	Number	Comment
2	Joe Schmo	10	N		<-- IF D is N, the cell in column E will remain blank
3	Betty Crocker	30	Y		<-- IF D is Y, this cell in column E will auto populate with 1200 (for example)
4	Exception Jones	30	Y		<-- IF D is Y, this cell in column E will auto populate with 1500 (for example)
5	Evil Knievel	20	Y		<-- IF D is Y, this cell in column E will auto populate with 1200 (for example)

<tbody>
</tbody>

I would put the predesignated number of a worksheet that holds data and pull from it. So in the table, column E should auto-populate as:
2 blank
3 1200
4 1500
5 1200

If you can figure out how to make this work, I would so greatly appreciate it.Thank you,
Tres

 

[Fluff]

How about
=IF(D2="N","",IF(AND(D2="Y",LEFT(B2,9)="Exception"),1500,1200))

 

[TresRing]

How about
=IF(D2="N","",IF(AND(D2="Y",LEFT(B2,9)="Exception"),1500,1200))

THAT WORKS!!!!!!!!!!!!!!!!!!! Thank you so very much!!!!!!!!!!!

 

[Fluff]

Glad to help & thanks for the feedback

 

[TresRing]

How do I give you credit for the answer?

 

[Joe4]

How do I give you credit for the answer?

That's really not the way this board works. There aren't points or anything like that.
However, you can click on the "Thank you for posting this" or the "Like this post" icons at the bottom of the helpful post, if you like.