In sheet 1 I have a column of project numbers, a column of project names, and
a column that might contain a "Y". Per this formula:

=IF($AD4="Y",HYPERLINK("#"&CELL("contents",'Dashbo ard Data'!I22),'Dashboard
Data'!J22),HLOOKUP("PROJECT_TITLE",TrackingGantt!$ A:$Z,MATCH($J4,TrackingGantt!$B:$B,FALSE),FALSE))

if there is a "Y" in the column then the project name field needs to be a
hyperlink created from sheet 2. I there is no "Y" then the project name is
pulled from a different sheet. The formula works fine as long as I have the
cell hard coded in it but I really need it to be a lookup based on the
project number in sheet 1 matching the project number in sheet 2 and then
pulling the refernce and friendly name from the array. How can I do this?

Thanks!

Randy