Help: Using text in Y Axis when creating Charts | General Excel Questions & Answers | Excel Forum

I am working on a dashboard but am stuck on the graph/ chart area.

My issue is that my data source has text instead of numbers and I cannot find a way to graph it.  My graph would show what training employees have completed. So I have 3 items in my source data (Not Required, Pending, Comp)

I have tried to add a another data source where maybe the text can be interpreted as a number for example I entered a lookup formula where

Not Req=0

Pending=1

Comp=2

My plan was to then graph the numbers and just enter a text box later in the graph and cover the numbered Y axis with the text box over it.  However the chart wont graph the numbers from that lookup formula. When I enter a number it does graph it though but not when the number in the cell is a product the lookup formula.

Im stuck here and need some help.  The file is attached for your review.