IF AND ELSE OR Statement Help requested

TresRing

New Member
Joined
Sep 12, 2017
Messages
16
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?
 
Upvote 0
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.
 
Upvote 0
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


[TABLE="class: grid, width: 900, align: left"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]Hours
[/TD]
[TD](Y/N)
[/TD]
[TD]Number
[/TD]
[TD]Comment
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Joe Schmo
[/TD]
[TD]10
[/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]<-- IF D is N, the cell in column E will remain blank
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Betty Crocker
[/TD]
[TD]30
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD]<-- IF D is Y, this cell in column E will auto populate with 1200 (for example)
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Exception Jones
[/TD]
[TD]30
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD]<-- IF D is Y, this cell in column E will auto populate with 1500 (for example)
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Evil Knievel
[/TD]
[TD]20
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD]<-- IF D is Y, this cell in column E will auto populate with 1200 (for example)
[/TD]
[/TR]
</tbody>[/TABLE]











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
 
Upvote 0
How about
=IF(D2="N","",IF(AND(D2="Y",LEFT(B2,9)="Exception"),1500,1200))
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top