hyperlink to cell based on formula results

[nba]

I am using a formula to set conditional formatting to display bookings on a
calender. I want to hyperlink from the calender sheet to the sheet that has
the data. My formula is

=SUM(SUMPRODUCT((Booking_Unit="r3")*(Arrival_Date=L$4)*(Nights>0))+SUMPRODUCT((Booking_Unit="r3")*(Arrival_Date<L$4)*(Departure_Date>L$4)*(Nights>0))+SUMPRODUCT((Booking_Unit="r3")*(Departure_Date=L$4)*(Nights>0)))

This formula gives a result of 1 or 2.

Can you hyperlink to a cell reference using a formula result?

 

[Jacob Skaria]

Try the below..Instead of formula try with a cell refernce and then once
tested change the cell reference to formula

=IF(formula=2,HYPERLINK("#"&CELL("address",B10),"target is relative"),
HYPERLINK("#"&CELL("address",B11),"target is relative"))

If this post helps click Yes

 

[Jacob Skaria]

Try...replace formula with your actual formula

=HYPERLINK("#"&CELL("address",
CHOOSE(formula,"",Sheet2!A1)),"Click me")

If formula result is 2 then the hyperlink will take you to Sheet2 cell A1
and if 1 it will return blank.. Try with a cell reference first

=HYPERLINK("#"&CELL("address",
CHOOSE(A1,"",Sheet2!A1)),"Click me")


If this post helps click Yes

 

[nba]

I tried your formula but I need to go to the cell reference that is found by
the formula. Eg G78. The formula below is set to return a number value for
conditional formatting but I would like to Hyperlink to the data or cell
reference returned by the formula. So instead of Sheet2!A1 as a constant I
want to goto a variable cell address based on the data match.

 

[Jacob Skaria]

OK.. Try the below

The below formula will check whether what is typed in cell A1 is present in
colH . If not returns blank. If present returns a hyperlink to the matching
cell..Try and feedback.

=IF(ISNA(MATCH(A1,H:H,0)),"",HYPERLINK("#"&CELL("address",
INDIRECT(ADDRESS(MATCH(A1,H:H,0),8))),"Click me"))


If this post helps click Yes