Hyperlink Woes - Please Help

[Guest]

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",'Dashboard 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

 

[Guest]

Updated information - I made changes to my formula per my example. Each
individual function works correctly but the link location part of the
hyperlink gives me an invalid error (yet each part within it works properly).
Do I have the syntax wrong or is this just not possible to do?

=IF($AD4="Y",HYPERLINK("#"&CELL("contents",VLOOKUP($J2,'Dashboard
Data'!$A$2:$J$96,9,FALSE)),VLOOKUP($J2,'Dashboard
Data'!$A$2:$J$96,10,FALSE)),HLOOKUP("PROJECT_TITLE",TrackingGantt!$A:$Z,MATCH($J4,TrackingGantt!$B:$B,FALSE),FALSE))

Thanks again!