help with multi-If statement

[Abouya]

Hello,

I have this excel form in wich a user/employee will request a purchase order. there is a button to send that email directly to the designated supervisors depending on the approval limits.

Say for example I'm A and i'm filling a PO. This if formula will check if the total is under or more than my approval limit
Po total < my approval Limit then cell return email 1
PO total> my approval Limit then return email 2

(If Po total > approval limit of person's email 2, then return email 3 as well)


i hope that this make sense, i tried with nested ifs but i have to compare each user/employer against all these criteria listed above.

is there a better way to do it, for example vba?!

Thanks.

 

[Abouya]

Hello,

I have this excel form in wich a user/employee will request a purchase order. there is a button to send that email directly to the designated supervisors depending on the approval limits.

Say for example I'm A and i'm filling a PO. This if formula will check if the total is under or more than my approval limit
Po total < my approval Limit then cell return email 1
PO total> my approval Limit then return email 2

(If Po total > approval limit of person's email 2, then return email 3 as well)


i hope that this make sense, i tried with nested ifs but i have to compare each user/employer against all these criteria listed above.

is there a better way to do it, for example vba?!

Thanks.

I'm getting the result using this nested if now:

=IF(AND(H6=ApprovedAmounts!A2,J26>ApprovedAmounts!B2),ApprovedAmounts!C5,ApprovedAmounts!C4)

However, this checks for one employee, how i can add this condition if H6=ApprovedAmounts!A3,J26>ApprovedAmounts!B3 and more conditions. They all have the same True and false result: True: ApprovedAmounts!C5 and False: ApprovedAmounts!C4.

Please help a brother out.

 

[sneuberg]

I see about eight different cells in your post and I can't tell what's what and what's suppose to happen. Please state more clearly what you want to accomplish. Especially what do you mean by more conditions and how these conditions play in the final result. It might help if you posted a truth table.

 

[Minty]

Assuming you have a list of your employees and their approval limits you should probably use a vlookup.
Or put it in a database and just link the tables...

 

[Abouya]

I see about eight different cells in your post and I can't tell what's what and what's suppose to happen. Please state more clearly what you want to accomplish. Especially what do you mean by more conditions and how these conditions play in the final result. It might help if you posted a truth table.

Thanks Sneuberg for responding. i have a table that holds employees names and their approval limits. Names are on ApprovedAmounts!A2:A16 and approval limits are on ApprovedAmounts!B2:B16. Plus, ApprovedAmounts!c2:c16 that mentions X2 or X1 supervisors for each employee.

Now, on spreadsheet "PO Requisition!" an employee will start filling information, he will chose his name then input quantity and all that...etc.

The total Po is in PO Requisition!J26.

What i'm trying to achieve is:

The list of employees have different superviros. Say

Cell A1 in PO Requisition! to return "office manager to input into system and proceed" or just return nothing if Employee/User approval limit <J26.

Cell A1 in PO Requisition! to return supervisor name "X2" if Employee/User approval limit >J26 and J26<X2 approval limit.

Cell A1 in PO Requisition! to return supervisor name "X3" if J26>supervisor approval limit.

I hope this makes sence. i tried with If(And) but doesn't work as intended.

 

[Abouya]

Assuming you have a list of your employees and their approval limits you should probably use a vlookup.
Or put it in a database and just link the tables...

Thanks for responding. How do i use the vlooup function here. could you please help me.

I have a table that holds employees names and their approval limits. Names are on ApprovedAmounts!A2:A16 and approval limits are on ApprovedAmounts!B2:B16. Plus, ApprovedAmounts!c2:c16 that mentions X2 or X1 supervisors for each employee.

Now, on spreadsheet "PO Requisition!" an employee will start filling information, he will chose his name then input quantity and all that...etc.

The total Po is in PO Requisition!J26.

What i'm trying to achieve is:

The list of employees have different superviros. Say

Cell A1 in PO Requisition! to return "office manager to input into system and proceed" or just return nothing if Employee/User approval limit <J26.

Cell A1 in PO Requisition! to return supervisor name "X2" if Employee/User approval limit >J26 and J26<X2 approval limit.

Cell A1 in PO Requisition! to return supervisor name "X3" if J26>supervisor approval limit.

I hope this makes sence. i tried with If(And) but doesn't work as intended.

 

[NBVC]

Is the Supervisor also listed in column ApprovedAmounts!$A$2:$A$16 as employees with limits?

Assuming yes and Assuming Employee enters his name in A2,

Then

=IF(VLOOKUP(A2,ApprovedAmounts!$A$2:$B$16,2,0)<=J26,"office manager to input into system and proceed",IF(AND(VLOOKUP(A2,ApprovedAmounts!$A$2:$B$16,2,0)>J26,VLOOKUP(X2,ApprovedAmounts!$A$2:$B$16,2,0)<=J26),X2,IF(VLOOKUP(X3,ApprovedAmounts!$A$2:$B$16,2,0)<=J26,X3,"")))