Hyperlink to cell with matching date

[Mandy_]

Hi,
I've created a day planner in excel office 365 - and one sheet has the dates in a month view (for six months), and the second sheet has the day planner with one date per page. I'm having trouble creating a hyperlink in the first sheet where when I click the date it it will take me to the page in the second sheet with the matching date. I'm trying command + K, but I can't type a formula. I've tried Hyperlink formula with CELL, INDEX and MATCH, but I can't seem to make it work... Thank you for your help.

 

[Alex Blakenburg]

In terms of using Ctrl+K did you click on "Place in this Document" ?
Mind you for that many dates a macro is probably going to be more practical but then we would need a lot more information on sheet names and placement of data in both sheets.

 

[Mandy_]

Thank you, I’m going to be saving the document as a pdf, and have the hyperlinks still working. Will a macro work with this? So the document will be in my pdf annotator.

Can I use the command +K as a formula to find and match the date? If so, which formula do I use please?

 

[Alex Blakenburg]

I've tried Hyperlink formula with CELL, INDEX and MATCH, but I can't seem to make it work...

The hyperlink formula would look something like the below, with the dates in Sheet2 being in column B.

Can I use the command +K as a formula to find and match the date?

No Ctrl+K does not have a formula option. The formula option uses the formula Hyperlink and looks like the below.

So the document will be in my pdf annotator.

I have no idea about making the Excel Hyperlink work in the PDF file created from it. That is a totally different question and you would be better served by asking that in a different thread.

20221112 Hyperlink Mandy_.xlsm
	A	B
1
2	1/11/2022	1/11/2022
3
4	2/11/2022	2/11/2022
Sheet1

Cell Formulas
Range	Formula
B2,B4	B2	=HYPERLINK("#'"&MID(CELL("address",INDEX(Sheet2!B:B,MATCH(A2,Sheet2!B:B,0),0)), FIND("]",CELL("address",INDEX(Sheet2!B:B,MATCH(A2,Sheet2!B:B,0),0)))+1, 100), A2)

 

[Mandy_]

Thank you!