hyperlink to cell based on formula results
 

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?

hyperlink to cell based on formula results
 

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

hyperlink to cell based on formula results
 

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?

hyperlink to cell based on formula results
 

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?

hyperlink to cell based on formula results
 

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?

hyperlink to cell based on formula results
 

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?