Hyperlink Cell stopped working after changing format

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Dear reader,

I'm trying to hyperlink a cell with a date in it, to jump to that date in another sheet. I've had it working but I changed the format from the cell from JJJJ/MM/DD to DD-MM-JJJJ, and now it stopped working.

I'm using this formula in the cell:
VBA Code:
=HYPERLINK("#"&CELL("address";INDEX(Rooster2021!B2:B3885;MATCH(TEXT(Registratie!B13;"dd-mm-jjjj");Rooster2021!B2:B3885;2)));"Klik voor datum in rooster")

I've tried changing the format in the formula aswell as in the other sheet with the dates multiple times but it doesn't seem to want to work. I'm not sure what I'm doing wrong.

Any help is much appreciated.
Kind regards.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,362
Office Version
  1. 2016
Platform
  1. Windows
Hi SteveNL86,

You MATCH has a match type of 2 but that is not an option. I believe a value of 2 is treated as a 1 so "match_type = 1 or omitted: MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order" which I don't think is what you want.

If the format of the dates in Rooster2021! is text then changing the match_type to 0 will find an exact match.

If the format of the dates in Rooster2021! is numeric date format then try changing the MATCH to:
MATCH(DATE(YEAR((Registratie!B13),MONTH((Registratie!B13),DAY((Registratie!B13)),Rooster2021!B2:B3885,0)
 
Solution

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Thank you Toadstool, you got me in the right direction.

I ended up changing it to:
VBA Code:
=HYPERLINK("#"&CELL("address";INDEX(Rooster2021!B2:B5000;MATCH(DATE(YEAR((Registratie!B13));MONTH((Registratie!B13));DAY((Registratie!B13)));Rooster2021!B2:B5000;2)));"Klik voor datum in rooster")

The reason I was using 2 was to get the last value in the list so you don't have to scroll when looking for all the dates.

Thanks for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,651
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top