|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 5th, 2009, 11:38 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
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_DateL$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? 
|
|
#2
November 5th, 2009, 11:53 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
|
|
#3
November 5th, 2009, 12:28 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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_DateL$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?
|
|
#4
November 5th, 2009, 12:45 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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_DateL$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?
|
|
#5
November 6th, 2009, 09:04 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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_DateL$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?
|
|
#6
November 8th, 2009, 05:21 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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_DateL$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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
