Hyperlink missing by set number of rows

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
145
I have 2 tables on separate sheets that each has its header starting at row 5. I have a formula that hyperlinks from a cell in one sheet to a corresponding cell in another sheet based on a value in a column on my first sheet. I'm using structured references in my formula which maybe the problem but I’m not sure if that’s the case.

This is the formula I'm using

=IF(ISERROR((HYPERLINK("#'Tender Calc'!" & ADDRESS(MATCH([@TTenderNo1], (TenderCalcTable[TCTenderNo1]), 0), 1), [@TTenderNo1]))),[@TTenderNo1],(HYPERLINK("#'Tender Calc'!" & ADDRESS(MATCH([@TTenderNo1],(TenderCalcTable[TCTenderNo1]), 0), 1), [@TTenderNo1])))

Sheet 1 is called Tenders
Sheet 2 is called Tender Calc

The formula uses the value in sheet 1 (on the same row) from column TTenderNo1 to hyperlink to the same number found in column TCTenderNo1 on sheet 2. The problem is that when the hyperlink is clicked, it selects a cell on sheet 2 which is in the correct column but 5 cells above the correct one.

It’s clearly to do with the table starting at Row 5 and I don't want to change this. It seems to work fine if I change my structured references to absolute cells references but I’m trying to make the whole thing dynamic.

Can anyone shed any light on how to fix the issue? I'm thinking ROW() may be the solution here, but I'm stumped with the syntax.

Thanks
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
608
I think the address( maybe forcing the incorrect row, whereas Match alone should return the correct row number if the match array is the right size?
 

Forum statistics

Threads
1,082,612
Messages
5,366,606
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top