How to update a cell with "if / and" condition

Shrestha

Hi, I am trying to figure out the automation or formula to auto update a cell with "if / and" conditions are met.

Please help me achieve this.

If Column 'Headset' values is = Column 'Asset ID' value and Status Column of same row of Column 'Headset' is 'Shipped', update Availably Column, same row of Column 'Asset ID' to not available.

Andrée Starå

Hi @Sugandha Joshi

Try something like this.

=IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped", "No", "Yes")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Sugandha Joshi

Here's another one with an error check.

=IFERROR(IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped", "No", "Yes"), "Error")

Bassam Khalil

Hi @Sugandha Joshi 

Hope you are fine, i will try to do it for you but could you please confirm that all column in one sheet or in deferent sheets.

Shrestha

@Bassam.M Khalil

Thank you for your swift action. Any workaround or formula would be a great help.

Best regards,

Andrée Starå

Hi @Sugandha Joshi

Try something like this.

=IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped", "No", "Yes")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Sugandha Joshi

Here's another one with an error check.

=IFERROR(IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped", "No", "Yes"), "Error")

Bassam Khalil

Hi @Sugandha Joshi 

Sorry for delay, please find in the following the soluation:

1- i create "Store Database"

2- I create "Shipping Database"

3- The Formula to check the Availability column ( Symbol Type ) status =IF(VLOOKUP([Asset ID]@row, {Shipping Database Status}, 3, 0) = "Shipped", "Red", "Green")

the following is a link to those database so you can check

• Store Database

https://publish.smartsheet.com/53898c31e7f541b18a377f3775a7b2de

• Shipping Database

https://publish.smartsheet.com/347a7e1b159040c1be41e06f52d79432

Shrestha

@Bassam.M Khalil

Thank you very much for your help and time. I really appreciate it. My data is all in one sheet.

@Andrée Starå

The formula worked perfectly fine and this is exactly what I was looking for.

Just one more question, how can I add one more condition within this formula?

e.g. "Shipped" OR "In Progress"

=IFERROR(IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped" OR "In Progress", "No", "Yes"), "Error") - this didn't work

Thank you in advance.

SJ

Andrée Starå

@Shrestha

I hope you're well and safe!

Excuse the late response.

Did you get it working?

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Shrestha

@Andrée Starå

I still need help on this.

how can I add one more condition within this formula?

e.g. "Shipped" OR "In Progress"

=IFERROR(IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped" OR "In Progress", "No", "Yes"), "Error") - this didn't work

Thank you in advance.

SJ