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?

 

[Jan Karel Pieterse]

Hi Nba,

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

Yes, by using the HYPERLINK worksheet function.

=HYPERLINK("#'Sheet1'!$A1") points to sheet1, cell A1.

As you can see the syntax is simple as long as you prepend the address with a #
and surround the sheetname with single quotes to ensure it works for sheets
with e.g. a space in their name.

That way, you can have the sheetname in a separate cell:

=HYPERLINK("#'" & B1 & "'!A1")

will point to a cell on the worksheet who's name you enter in cell B1.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[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