mardi 14 juillet 2015

Pull a random value from a list if another column has a value in same row

I have a list of named entities and a chart of how often they have been audited:

Name   May  June  July  Aug
Alpha  2    1     1     
Beta   1    1     
Gamma  1    1     1
Delta  1    1     

I'm trying to have a cell display who in a given month wasn't audited at least once.

The best I have managed has been using two cells, one named as "RandNum" with

=INDEX(A2:A5,RANDBETWEEN(1,COUNTA(A2:A5)))

and another cell that i use to get a random returned value, if the value it returned has been audited for that month it says "Try Again." In example 4 is the column for July. When I hit delete on a blank cell it updates.

=IF(VLOOKUP(RandA,A2:E5,4)>=1,RandNum,"Try Again")

I'd rather not resort to VBA if I can help it but I'm open to it.




Aucun commentaire:

Enregistrer un commentaire