mardi 30 janvier 2018

Excel: Combining nested IF statements with OR, without using a macro

I'm trying to basically create a mandatory field in Excel without using VBA/macros.

I am creating a spreadsheet listing employees with unused vacation hours above a maximum cap. It will go to the employees' managers who will then approve or deny the employee's request to use the additional hours. The manager will select from a drop down list of pre-populated reasons (sheet 2 A1:A13) to approve or deny the request.

enter image description here

enter image description here

There is a two part validation:

  1. If no approval reason is selected, and hours are entered in column H, validation fails:

=IF(AND((ISNA(VLOOKUP($I$2,Sheet2!$A$1:$A$7,1,FALSE))),H2<>0),"ERROR",H2)

  1. If no denial reason is selected, and the value of column H is zero, validation fails:

=IF(AND((ISNA(VLOOKUP($I$2,Sheet2!$A$9:$A$13,1,FALSE))),H2=0),"ERROR",H2)

I can get these two formulas to work independently, but can’t figure out how to combine them into a single formula using an “OR” statement. Is it possible to do this?

(I know an alternative is to add “true/false” columns for both formulas, then create another IF/AND formula to validate that both conditions are met and then hide the columns, but looking for a cleaner solution).

I specifically don't want to embed macros because this will be sent out by email as a saved document, and the recipients will be prompted whether to activate the macros.

Aucun commentaire:

Enregistrer un commentaire