Auto change a cell based on a checkbox in a different cell (all in the same row though) or change in

Diana.Martinez.Intern

I created a pretty intense sheet to keep track on invoices and receipts. I have an "Action" column that is a drop down column. Here are a few of those "Actions"

Request Sponsorship

Submit Sponsorship

Request Invoice

Call to pay with CC

Call back to pay

Submit Check Request

Follow-Up Check Approval

Mail Check Out

Request Receipt

For example, when I put a date in the "Received Sponsorship" column, I want the "Action" to automatically change to "Submit Sponsorship"

When I put a date in "Sent Sponsorship" column, I want the "Action" to automatically update to "Request Invoice" 

When I put a date in "Received Invoice" column, I want the "Action" to automatically update to "Call to pay with CC" (if "Payment Type" is CC) or update to "Submit Check Request" (if "Payment type is "Check") 

Does anyone have any ideas? I know that's a few formulas? or Conditional formatting? or Dependencies? Not sure! 

 

Thanks!

Andrée Starå

Hi Diana,

Try something like this.

Add the formula below to the Action column. When you continue the formula it should be set up from the end because when an IF is true it will continue to check the rest. That is why my example shows the Received Invoice first.

=IF(AND(ISDATE([Received Invoice]@row); [Payment Type]@row = "Check"); "Submit Check Request"; IF(AND(ISDATE([Received Invoice]@row); [Payment Type]@row = "CC"); "Call to pay with CC"; IF(ISDATE([Sent Sponsorship]@row); "Request Invoice"; IF(ISDATE([Received Sponsorship]@row); "Submit Sponsorship"))))

The same version but with the below changes for your and others convenience.

=IF(AND(ISDATE([Received Invoice]@row), [Payment Type]@row = "Check"), "Submit Check Request", IF(AND(ISDATE([Received Invoice]@row), [Payment Type]@row = "CC"), "Call to pay with CC", IF(ISDATE([Sent Sponsorship]@row), "Request Invoice", IF(ISDATE([Received Sponsorship]@row), "Submit Sponsorship"))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting