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("a ddress",
INDIRECT(ADDRESS(MATCH(A1,H:H,0),8))),"Click me"))


If this post helps click Yes
---------------
Jacob Skaria


"nba" wrote:

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" wrote:

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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),"t arget is relative"),
HYPERLINK("#"&CELL("address",B11),"target is relative"))

If this post helps click Yes
---------------
Jacob Skaria


"nba" wrote:

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)*(Nights0))+SUMPRODUCT((Booking_Unit="r3")*(A rrival_Date<L$4)*(Departure_DateL$4)*(Nights0))+ SUMPRODUCT((Booking_Unit="r3")*(Departure_Date=L$4 )*(Nights0)))

This formula gives a result of 1 or 2.

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