I am not sure if I am using the correct function. I have 14 separate
worksheets. Each worksheet represents a specific project and uses the same
data range as all of the other worksheets.

I want to use a drop down list of all the different project names. A
formula will use the value selected from the drop down and return the value
on that worksheet in a specified cell.

ie. Worksheet names: Alpha, Beta, Cappa

Alpha cell $a$1 = 500
Beta cell $a$1 = 100
Cappa cell $a$1 = 200
******************
Reporting Summary Worksheet
Drop down selection: Beta

Formula returns: 100