Assigning a value to another Column from a formula...

Question

Hello All,

I hope you are all well and safe.

I have a sheet that has the following columns: 1st Level Approver, 2nd Level Approval Required, 2nd Level Approver, 1st Approval, 2nd Approval columns and Helper.

1st Approval and 2nd Approval are Single Select drop downs with the options "Approved" and "Declined"

I need these to be selectable in update requests by the 1st and 2nd Level Approvers.

I want to add a formula to the Helper column  =IF([1st Level Approver]@row = [2nd Level Approver]@row, 1st Level Approval]@row = "Approved")

I keep getting a #INVALID COLUMN VALUE error in the formula.  I have recreated it on another sheet with simpler columns types and get the same thing.

My question is: Can we assign a separate column a value from a formula within another column?

(I also thought it would be nice to be able to have multiple True and false outcomes.  ie. IF this then 1 and 2 and 3 else A and B and C)

Thanks for any advice.

Robert Meisch

Mgr. Deployment Operation

SYSCO Corp.

bcwilson.ca · Accepted Answer

I think I understand what you are doing

You should simply put the formula in the column you want to set. It looks like you are trying to code a variable.. as in programming   'Set [1st Level Approval]@row = "Approved" '

If you put the below statement in the helper column it would return "Approved" when they match..

=IF([1st Level Approver]@row = [2nd Level Approver]@row, "Approved")

Be careful with your logic though..

Your statement would also return "Approved" if they are both Blank and also if they are both Declined

Better to do something like

=IF(AND([1st Level Approver]@row = "Approved", [2nd Level Approver]@row = "Approved"), "Approved")

And yes you can do nested IF Statements.. This is an example from a Project report that picks, Green, Red, Yellow and, Grey depending on case

=IF([% Complete]@row = 1, "Green", IF([Finish Date]@row - TODAY() <= 7, "Red", IF([Finish Date]@row - TODAY() <= 14, "Yellow", "Grey")))

bcwilson.ca · Accepted Answer

So if the 2nd approver has approved the 1st approver is overruled?

bcwilson.ca · Accepted Answer

@RobertMeisch@Sysco

Actually, that is spot on now that you mention it..

I have an Approval Matrix with names as well as dollar level approvals.. I bring those in via a Vlookup into contact fields and have the workflow do the logic to determine who it goes to...

I created a "Workflow" Column that tells me what approval workflow the ticket should follow in terms of approvers and then populates that. So if the workflow is for service, parts, sales, etc.. it goes through the correct approvals based on the vlookups to the table.

It is also good for moves in the company as I don't have to go update all the tickets I just update the matrix and they repopulate.. Have to watch the ones sitting when I do that though

Best of luck.. let me know how you make out