Point Hyperlink to specific cell with conditional

[Schturman]

Hi to all
I need some help to fix a formula that I use from this post.
I have some sheets with dates in column A (pic 1) and some in rows (pic 2)
I need that a hyperlink will jump to a date TODAY() if exist in pointed sheet and if not exist it will just point to A2
I tried like this:

=HYPERLINK("#'"&L2&"'!"&ADDRESS(1,(WORKDAY.INTL(TODAY(),0)),"Open link")

but it not working...
L2 - the name of sheet
Can someone help me create correct formula ?
Thanks

 

[Schturman]

Hi
I found a way how to do this...
For sheets like in pic 1 where all dates in one column A, I can use:

=HYPERLINK("#'"&L2&"'!"&ADDRESS(ROW(XLOOKUP(TODAY(),INDIRECT("'"&L2&"'!A3:A126"),INDIRECT("'"&L2&"'!A3:A126"))),1,4),"Open link")

For sheets like in pic 2 where all dates in a few rows, I can use:

=HYPERLINK("#'"&L2&"'!"&ADDRESS(ROWS({3;12;21;30;39;48;57;66;75;84;93;102}),COLUMN(XLOOKUP(TODAY(),INDIRECT("'"&L2&"'!A3:AE3"),INDIRECT("'"&L2&"'!A3:AE3"))),4),"Open link")

It working but for some reasons jump to next month... For example today is 17/01 but link jump to cell 17/02...
I don't understand why...
I tried to add "-9" to the row formula

ROWS({3;12;21;30;39;48;57;66;75;84;93;102})-9

Now it jump correctly to 17/01
same if I do:

ROWS({3,12,21,30,39,48,57,66,75,84,93,102})+2

or

ROWS(A3:AE102)/27

or

ROW()/22

but I'm not sure if it will work for example when today is 20/04...
I tried to check with another date DATE(2025,04,20) instead TODAY(), but get #N/A instead link
Can someone check and fix this formula if needed ? Or point me how to check correctly ?
Thanks