Excel formula - cell index match - but want answer to be direct cell reference

[Mandy_10]

Hi,

I have a day planner where in excel you click the date and you are taken to that days day planner (in a separate sheet).  The formula I have used uses cell index and match and so searches for the matching date and takes you to that cell.  I want this planner to be in PDF form so I can use it in a pdf reader (but have the links still working).  I've tried exporting via Adobe, and it doesn't work.  I've spoken to Adobe and they have indicated their program isn't currently available to run formulas.  But that Adobe will work if it has a direct cell link.  I can't change the day planner dates to be linked to a certain cell for next year, that date will be on a different day.  For example - 1st January 2023 is a Sunday and 1st January 2024 is a Monday and that would mean changing 365 cell references...

So - how to I change my hyperlink formula to return the cell value of what the formula is calculating so when I export the excel file via Adobe for PDF, the links are the exact cell reference, and thus will work in the export.

Thank you!

[#2]

I've managed to get the cell address, using a helper column

The answer displays as: '[Sample - Sunday Start Daily Digital Planner.xlsx]Part Two - day planners'!$A$2

This is the correct cell. But I am struggling to create a hyperlink to this cell. As I'll have 365 formulas, I don't with to use command +K.

I've tried the hyperlink formula, but maybe my syntax isn't quite right. I can't get it to link to the cell address that has been generated.