Update a cell from the list with condition

Shrestha

Hello Experts,

How to auto update a cell from the list when conditions are met?

e.g. (screenshot) If the status is 'In Progress' - Look for the first available gear in the list and update a cell of a row (In Progress) with the first available gear ID.

Please Help!!

Paul Newcome

Try something like this...

=IF(Status@row = "In Progress", INDEX({Source Data ID Column}, MATCH("Yes", {Source Data Availability Column}, 0)))

Shrestha

Hello @Paul Newcome

Thank you for looking into this.

This is returning error. (Screenshot)

FYI, I have another formula running in Availability "=IFERROR(IF(INDEX(Status:Status, MATCH(ID@row, Gear:Gear, 0)) = "Shipped", "No", "Yes"), "Yes")"

Please advise!!

Shrestha

To simplify, I just want Gear cells(and below) to be filled with first available ID but only when the same row is populated with entries received from Form.

Paul Newcome

Is this all in the same sheet?

Shrestha

Yes, this is all in the same sheet.

Paul Newcome

Sorry about that. I misunderstood what was going on.

=INDEX(CHILDREN(ID$1), MATCH("Yes", CHILDREN(Availability$1), 0))

Shrestha

Hello @Paul Newcome ,

Still no luck.

The Gear column cell below Row20 should be populated with value, if Availability is "yes" then copy Gear Item from ID column and paster it to Gear21 cell. Trigger this only when Status has any value (e.g. Status21)

Gear 21 should be blank until there is no input at Status21.

Hope this explains what am i trying to achieve.

I can share you my sandbox if you are want to see the actual sheet.

Best regards,

Paul Newcome

The problem is in the Availability column in the top portion. That formula is causing a circular reference which is blocking anything else from running. You may need to adjust which columns your data is in so that the Kit in the bottom portion is in the ID column and the Requested By section of the bottom portion is in the Gear column.