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

Robert Meisch

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

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

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

bcwilson.ca

@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

bcwilson.ca

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")))

Robert Meisch

Yeah, I understand that but I still want folks to be able to just select "Approved" or "Declined" in the 1st level approval column and the formula in the column would prevent that.

However, I think you may be steering me towards a third Approval column that would evaluate the first two and include the predicament outlined above. It will force me to adjust the workflow to accommodate.

Thanks for the advice.

bcwilson.ca

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

Robert Meisch

Possibly, but was really trying to solve for when the submitter = the 1st level approver. I was hoping to auto-approve in that scenario.

But you have made me consider a new avenue: I am using an Index Match looking at another table to populate the 1st & 2nd level approvers. What if during that logic I check and if they are equal I adjust at that point...

That may work better.

Thanks.

bcwilson.ca

@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