Forum Discussion

Copper Contributor

Sep 19, 2023

If, and, isnumber, match, hyperlink

Hi there! I am stuck with a task. I need to be able to deactivate a hyperlink (to another sheet) if the conditions are met, and activate it if not. Help please!

excel

Formulas and Functions

AngelaMarieCarumay

Copper Contributor

Sep 19, 2023

Hi Martin_Weiss,

=if(B6=today(),"Unavailable",if(ISNUMBER(MATCH(D9,'Sheet 2',0)),"Full",HYPERLINK([sheet 2],"Book")))

Conditions:
1. If the date entered is the same as today's date, then the activity is "Unavailable".
2. If the date entered is after today:
a. If the time entered matches the time in Sheet 2, then "Full" and no hyperlink
b. If the time entered does not match the time in Sheet 2, then "Book" hyperlinked to Sheet 2

Not sure if it makes sense 😞

Thank you very much.

Martin_Weiss

Bronze Contributor

Sep 20, 2023

Hi AngelaMarieCarumay

thanks for the additional information.

The issue is, that even if already the first criteria is met and the formula delivers "unavailable", it creates a invalid hyperlink.

So my proposal would be to create also for "unavailable" and "full" a hyperlink which directs only to the current cell. So in fact, there would be a hyperlink that does nothing.

Maybe try this:

=IF(B6=TODAY(),HYPERLINK("#","Unavailable"),IF(ISNUMBER(MATCH(D9,'Sheet 2'!B2:B20,0)),HYPERLINK("#","Full"),HYPERLINK("#'Sheet 2'!A1","Book")))

Please note, that this formula only checks the time in D9 against the times in range B2:B20, but it does not consider if the date in A2 equals the booking date.