Excel dynamic hyperlink sheet navigation

0
votes

I have a dynamic hyperlink that selects the last cell in a specified column within a sheet, regardless of th number of items in that column. So if I have 22 items in a column, the link will select the 23rd row, etc.

What I am trying to do is have it select the last cell (in the same fashion), but within a different sheet of the same workbook - provided that my reference cell is set to the appropriate number (i.e. I have a dropdown selector which sets the value of this reference cell and that changes the hyperlink.

I implemented this using an IF and a COUNTA statement, but I can't figure out how to make the link formula count the number of items in columns of different sheets. Here's the function:

=IF(J6=1, HYPERLINK("#Parts!b"&COUNTA(B:B)+3, "Add New Entry"), HYPERLINK("#Employees!b"&COUNTA(B:B)+3, "Add New Entry"))

How do I change the formula so that the COUNTA is executed in the appropriate worksheet?

1
excelhyperlink

1 Answers

1
votes

Just add your sheet name! in front of the column range you've defined in counta(). For instance if your sheet's name is sheet1 you would do the following: 

=IF(J6=1, HYPERLINK("#Parts!b"&COUNTA(sheet1!B:B)+3, "Add New Entry"), HYPERLINK("#Employees!b"&COUNTA(sheet1!B:B)+3, "Add New Entry"))