• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DYNAMIC HYPERLINK

A

amabdullah

New Member
HI,

I have individual sheet of each employee with several column among that one column is of the complete year dates.I have another index sheet where all the employee names are mentioned. At the index sheet when I select an employee then by using vlookup and hyperlink formula "=HYPERLINK("#"&A1&"!a2")" i reach to that employee sheet but to cell A1. What I want that once the employee is selected i can go to his sheet at column where todays date is mentioned. Can you please help me?
 
Hi amabdullah,


Here's a way to do it :

- change your hyperlink formula to : =HYPERLINK("#"&A1&"!"&ADDRESS(D1,D2))

. A1 : sheet name of the employee selected

. D1 : formula that gives you the row of the actual date

. assuming that your dates are in column C, the formula in D1 should be :

=SUMPRODUCT((INDIRECT(A1&"!C1:C500")=TODAY())*ROW(INDIRECT(A1&"!C1:C500")))

. D2 is the column number of your dates (here I assumed always column C)


Adjust as required.
 
You must log in or register to reply here.
Back
Top