HYPERLINK

[Marvo]

The following formula results in a cell with a date inside. How do I add a HYPERLINK to take me to the resulting cell please?

=IF(C21="","",XLOOKUP(C21,ALL!$AH$2:$AH$5558,ALL!$B$2:$B$5558,,0,-1))
.

 

[bobsan42]

try like this:

=IF(C21="","",HYPERLINK("[.\]'Sheet1'!"&CELL("address",INDEX(ALL!$B$2:$B$5558,MATCH(C21,ALL!$AH$2:$AH$5558,0))),XLOOKUP(C21,ALL!$AH$2:$AH$5558,ALL!$B$2:$B$5558,,0,-1)))

 

[Marvo]

It returns "reference isn't valid". However if I hover over the cell the reference is correct.

I have a similar hyperlink with a different formula on another page that works and the only difference when you hover over the cell is that yours has [.\] at the start whereas the one that works has #. Has that got something to do with it?

I tried to use how that hyperlink was set up to do this one but failed, miserably.

 

[FormR]

Hi, here's another option you can try:

=IF(C21="","",LET(XL,XLOOKUP(C21,ALL!$AH$2:$AH$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))

 

[Marvo]

Yes that works, fantastic. Thank you so much, I'll get to work on the other cells. Another small improvement.

 

[bobsan42]

It returns "reference isn't valid". However if I hover over the cell the reference is correct

Replace sheet1 with ALL.
My mistake.