Is there a way to use the "value" of a name in a drop-down list to reference
a sheet tab name in a formula?

A1 has a drop down list. When a name (Bob's Sales) is selected from the
drop down list, B1 shows the value of cell D5 from sheet "Bob's Sales".

Thanks for your help!!