Using an array formula as a hyperlink cell reference

[travis]

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

 

[Gary''s Student]

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

 

[travis]

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

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&'Sheetname'!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

 

[Gary''s Student]

I did not make myself clear.

We are talking about two SEPARATE cells.

In cell A1 there is a formula that displays something like:

#'Sheet 2'!H24

In the OTHER cell (say B9) is the formula:

=HYPERLINK(A1)