#N/A on formula - Why??

[traceylreed]

Hello,

I have been using the attached spreadsheet so I can randomly choose associates to work in the areas they are certified in. I have the little green "triangle" in the left corner and it's giving me an N/A instead of a 0 (like the second "vacation" slot does). I have gone through the formula and can't find the issue.

Could someone please take a look and see if you could figure out what is wrong with it?

Thanks for your help!

[Fotis1991]

Hi

Try in this way

=IFERROR(INDEX(INDEX('Certified Employee Areas'!$A$2:$AD$100,,MATCH(A2,'Certified Employee Areas'!$A$1:$AD$1,0)),MATCH(TRUE,INDEX(COUNTIF($B$1:B1,INDEX('Certified Employee Areas'!$A$2:$AD$100,,MATCH(A2,'Certified Employee Areas'!$A$1:$AD$1,0)))=0,),0)),0)

[traceylreed]

Fotis1991,

Thank you; however, if I use that formula, it places the same person (the one in the vacation "slot") in each cell that has the "green triangle". If I change the "countif" from B1:B1 as it is listed in your formula, it then gives me a "0" in the cell but it does not randomly choose anyone into that cell when the macro is run. I need for it to still choose one of the associates. Depending on who is on vacation, and depending on who we have on the schedule (on sheet 3 - without a formula), there will be up to 3-4 spots empty, but I would like it to show as a "0" (or simply remain empty) and not show an N/A in the empty spots.

Does that make sense??