Help With Dynamic Hyperlink

[nutrastat]

This HYPERLINK is driving my crazy, hope you can help, as I have all but this one working.

On a sheet called 'ActiveInspections' I have created Tables. To the side of the table is a formula, which effectively says...if table is empty, show text 'Empty Table'. This is a hidden flag, but works with this Hyperlink...

=IFERROR(HYPERLINK("#"&ADDRESS(MATCH("Empty Table",A30:A1483,0)+(23),20,4,1,A1),"Þ"),"Þ")

Where I search for the Match "Empty Table", and with the row & column numbers it finds the string, and put the curser to the first cell of the next empty table, there are 160 Tables, and this works fine. The "Þ" is because I am using a Wingdings font, which is an arrow in a circle, just in case you were wondering.

However, I want to do the same from another page (Dashboard), and I have most of it working where it will go to the ActiveInspections page...

=HYPERLINK("#"&ADDRESS(1,20,4,1,Var_Sht_Name_AI),"Ä")

The Hyperlink will take you to the Active Inspection sheet (Variable: Var_Sht_Name_AI = ActiveInspections), on Row 1, Column 20.

However, I need to change the Row 1 to MATCH("Empty Table",A30:A1483,0)+(23) so that it will give the appropriate Row number, rather than 1.

Unfortunately, it says it does not match, appearing to be checking the current page, rather than the ActiveInspection sheet.

I have tried using INDIRECT, but cannot see what I have done wrong. Can somebody please help me - thanks??

 

[FormR]

Hi, if you want to use the named range to define the sheet name, then you could try like this.

=HYPERLINK("#"&ADDRESS(MATCH("Empty Table",INDIRECT("'"&Var_Sht_Name_AI&"'!A30:A1483"),0)+(23),20,4,1,Var_Sht_Name_AI),"Ä")

 

[nutrastat]

Thank you so much, this works. I put the INDIRECT in the wrong place in the formula, but could not see it. Thanks for your help & time.