Value based on multiple criteria

trimiii

Board Regular
Joined
May 15, 2018
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hi,

I need help... please see the picture attached...

column A; A - Date
column B - team#
column C - status
column D - assigned location
column E - Current location
column F - Actual location, only if the value on the columns e:e is OTHERS

so, I need your assistance example, for the 1st October, for team# 116 the assigned location is Garage 3 but this team was deployed on other than assigned location, this case "others", then if the value is others in the column E (current location, the user will enter the actual location column F example Shop3 in this case)


from G7 - P7 I have all the team# and need to show the result as follows:
Team 14 is working in the assigned location, Garage 1
Team 115 same
Team116 is not working on the assigned location but in the Shop 1; once I enter OTHERS in the current location, I need result from the next column (cells highlighted in yellow just for easy look)

any help... or thoughts?
 

Attachments

  • excel 72.JPG
    excel 72.JPG
    124.6 KB · Views: 8

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Can you give us the sample data with XL2BB so that we can easily copy it for testing?
 
Upvote 0
excel72.xlsx
ABCDEFGHIJKLMNOP
1Date#StatusAssigned LocationCurrent LocationACTUAL Location Others - if column G is others than new location entered
21-Oct-20114DeployedGarage 1Garage 1Daily AccountabilityOthers
31-Oct-20115DeployedGarage 2Garage 2
41-Oct-20116DeployedGarage 3OthersShop3Date1-Oct-20
51-Oct-20117DeployedGarage 4OthersShop4
61-Oct-20118DeployedGarage 5OthersShop5114115116117118119120121122123
71-Oct-20119DeployedGarage 6Garage 6Garage 1Garage 2Shop3Shop4Shop5Garage 6Garage 7Garage 8Garage 9Garage 10
81-Oct-20120DeployedGarage 7Garage 7
91-Oct-20121DeployedGarage 8Garage 8
101-Oct-20122DeployedGarage 9Garage 9
111-Oct-20123DeployedGarage 10Garage 10
122-Oct-20114DeployedGarage 1OthersShop 12Daily AccountabilityOthers
132-Oct-20115DeployedGarage 2Garage 2
142-Oct-20116DeployedGarage 3OthersShop35Date2-Oct-20
152-Oct-20117DeployedGarage 4OthersShop44
162-Oct-20118DeployedGarage 5OthersShop55114115116117118119120121122123
172-Oct-20119DeployedGarage 6Garage 6Shop12Garage 2Shop35Shop44Shop55Garage 6Garage 7Garage 8Garage 9Garage 10
182-Oct-20120DeployedGarage 7Garage 7
192-Oct-20121DeployedGarage 8Garage 8
202-Oct-20122DeployedGarage 9Garage 9
212-Oct-20123DeployedGarage 10Garage 10
Garage list
Cell Formulas
RangeFormula
A3:A4,A6:A11,A13:A14,A16:A21A3=A2
A5,A15A5=A3
A12A12=A11+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1:D1Cell Value="Indirect"textNO
C1:D1Expression=C1=D1textNO
Cells with Data Validation
CellAllowCriteria
E2:E21List='C:\Users\JasharK\Desktop\[Kusha version_ANA - LTC Kabul 201st_Gamberi - Daily_Training _Report 01Oct20.xlsx]Dropdown DO NOT EDITorDELETE'!#REF!
C2:C21List='C:\Users\JasharK\Desktop\[Kusha version_ANA - LTC Kabul 201st_Gamberi - Daily_Training _Report 01Oct20.xlsx]Dropdown DO NOT EDITorDELETE'!#REF!
 
Upvote 0
Thanks for the XL2BB data. See if this does what you want.

trimiii 1.xlsm
ABCDEFGHIJKLMNOP
1Date#StatusAssigned LocationCurrent LocationACTUAL Location Others - if column G is others than new location entered
21-Oct-20114DeployedGarage 1Garage 1Daily AccountabilityOthers
31-Oct-20115DeployedGarage 2Garage 2
41-Oct-20116DeployedGarage 3OthersShop3Date1-Oct-20
51-Oct-20117DeployedGarage 4OthersShop4
61-Oct-20118DeployedGarage 5OthersShop5114115116117118119120121122123
71-Oct-20119DeployedGarage 6Garage 6Garage 1Garage 2Shop3Shop4Shop5Garage 6Garage 7Garage 8Garage 9Garage 10
81-Oct-20120DeployedGarage 7Garage 7
91-Oct-20121DeployedGarage 8Garage 8
101-Oct-20122DeployedGarage 9Garage 9
111-Oct-20123DeployedGarage 10Garage 10
122-Oct-20114DeployedGarage 1OthersShop 12Daily AccountabilityOthers
132-Oct-20115DeployedGarage 2Garage 2
142-Oct-20116DeployedGarage 3OthersShop35Date2-Oct-20
152-Oct-20117DeployedGarage 4OthersShop44
162-Oct-20118DeployedGarage 5OthersShop55114115116117118119120121122123
172-Oct-20119DeployedGarage 6Garage 6Shop 12Garage 2Shop35Shop44Shop55Garage 6Garage 7Garage 8Garage 9Garage 10
182-Oct-20120DeployedGarage 7Garage 7
192-Oct-20121DeployedGarage 8Garage 8
202-Oct-20122DeployedGarage 9Garage 9
212-Oct-20123DeployedGarage 10Garage 10
Sheet1
Cell Formulas
RangeFormula
G7:P7,G17:P17G7=LOOKUP("zzz",FILTER($E$2:$F$21,($A$2:$A$21=$L4)*($B$2:$B$21=G6)))
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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