CODEWITHSUNDEEP
google-sheets
rudy
rudy

Reputation: 1

Trying to use the IF or IFS formula for work problem

Hi Im trying to set up a formula to automate updates for orders. I attached the link for an example file.

We are trying to get the status column to changed based off of Column D, H, I, M, N There are 4 statuses that can be chosen from a drop down. Available Dispatched Picked Up Delivered

I was trying to use the IFS formula to make it so that the Status is marked Available if all columns are blank, Dispatched if Column D had a driver assigned, Picked up if Column H and I were filled, then Complete if Column was filled.

I tried the following formula but get NA =IFS(D3="", "Available",H3="", "Dispatched", I3="", "Dispatched", M3="", "Dispatched", N3="*", "Delivered")

Anyone that can help with this? Thank you.

https://docs.google.com/spreadsheets/d/1XsOsuvf_gZEWYMSw-q8MQHEt2Ky8XRV-o_XIGeSFLvo/edit?usp=sharing

Updated Link.

Upvotes: 0

Views: 32

Answers (1)

player0
player0

Reputation: 1

use in row 3:

=ARRAYFORMULA(
 IF(LEN(D3:D&H3:H&I3:I&M3:M&N3:N)=0; "Available"; 
 IF(D3:D<>""; "Dispatched";
 IF((H3:H<>"")*(I3:I<>""); "Picked"; "Complete"))))

Upvotes: 1

Related Questions