Forum Discussion
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!
3 Replies
could you be a bit more precise on the exact criteria, it would be easier to provide a good solution (maybe a screenshot etc.)
Generally spoken, the answer would look like this:
=IF(logical_test,HYPERLINK(...),something_else)
So, if the logical test results in TRUE, the hyperlink function will be used. Otherwise something else (e.g. a blank cell, a comment, a value etc.)
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.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.
