Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an array formula as a hyperlink cell reference
The following array formula gives as output the name of a cell
{="'Sheet 2'!H"&MATCH(C1&D$39,'Sheet 2'!A:A&'Sheet 2'!C:C,0)} Where C1 and D$39 are an array variable concatenated out of two different columns and Sheet 2 is a sheet I'm searching for that combination of variables (i.e. a payment by a certain payer on a certain date). The result of this particular formula is 'Sheet 2'! H842 because that particular combination of values occurs in row 842. I want to put the cursor into 'Sheet 2'!H842 with a hyperlink, or something like a hyperlink, so I can put a column of "Click to go to this payment" links next to a pivot table, or better yet inside the pivot table itself. But my attempts to use this formula as a cell reference aren't working, perhaps because arrays don't work in hyperlink cell references. So how do I do it? Travis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an array formula as a hyperlink cell reference
Hi Travis
First update the array formula to put a # in front of it: ="#" & your_formula If the above is in cell A1, then =HYPERLINK(A1) will produce a nice, clickable, hyperlink -- Gary''s Student - gsnu2007k |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an array formula as a hyperlink cell reference
On Oct 16, 9:09*pm, Gary''s Student
wrote: Hi Travis First update the array formula to put a # in front of it: ="#" & your_formula If the above is in cell A1, then =HYPERLINK(A1) will produce a nice, clickable, hyperlink -- Gary''s Student - gsnu2007k It didn't work, so I'll just make sure I'm doing exactly the right thing... The formula is: ="'Sheetname'!H"&MATCH(C44&D$39,'Sheetname'!A:A&'S heetname'!C:C,0) That results in the following, which is the cell reference I want to point my hyperlink at. 'Sheetname'!H24 If I'm understanding you, I need to change the formula to: ="#"&"'Sheetname'!H"&MATCH(C44&D$39,'Sheetname'!A: A&'Sheetname'!C:C,0) Resulting in the cell having the following value: #'Sheetname'!H24 I do a hyperlink and point it at the cell that says #'Sheetname'!H24, this should redirect me to 'Sheetname'!H24 But all that happens is the cursor sits there in the cell with the formula. Travis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set the hyperlink to reference a cell for the file name? | Excel Worksheet Functions | |||
Hyperlink - Passing a cell reference | Excel Worksheet Functions | |||
script to hyperlink and reference a cell value in the hyperlink | Excel Programming | |||
How can I use a cell reference in Sumproduct array formula? | Excel Discussion (Misc queries) | |||
Using a formula in a Hyperlink Cell reference | Excel Discussion (Misc queries) |