=IF(A1="","",IF(A1="No","N/A",HYPERLINK("#"&IF(A1="Yes- Promotion Proposed","'Yes- Promotion Proposed'!A1","'Yes - Business Critical'!A1"),A1)))

Try the above to eliminate the 0 when A1 is blank.

the red/bolded A1 is the "Friendly Name" I've chosen, which is the entry chosen in cell A1. I am not sure what you would rather have?