Hyperlink to particular cell of different worksheet based on content

[squib]

Hi all,
I have created a codebook type workbook with 10 different worksheets. Each of these worksheets has frequency tables of variables from a survey.

For example the first worksheet called "S1" has information about the variables from the first section of the survey. This includes the variable name (Column B), the question text (Column C), and a table of responses.

I now want to create a separate worksheet which will be the table of contents, and which will contain all the variable names (1000+) in the survey.

I need people to be able to simply click on a particular variable name in the table of contents (in Column A) and for this to hyperlink them to the correct cell in the correct worksheet. So I basically need to find a way to hyperlink IF the content of cell X in Column A in the table of contents worksheet (Sheet1) matches the exact same variable name in Column B of "S1".

I hope I explained my problem enough.....

Cheers
Anna

 

[GlennUK]

How do you know that the content of cell X will be in Column B of sheet "S1", and not in sheet "S2" or "S3" or any other sheet? ( you've said there will be 10 worksheets )

Here's an example of a hyperlink formula doing exactly what you ask:

=HYPERLINK("#S1!B"&MATCH(A7,'S1'!$B$1:$B$26,0),"Goto location...")

... note that it links to sheet S1.

 

[squib]

Hi Glenn,
Thank you very much for that solution. That is exactly what I was looking for

My table of contents is split into different headings showing the variables in each section. So I was just planning on running the function you gave me on all the variables in section 1 (found in worksheet "S1"), and then modify the formula when working on the other worksheets.
No doubt there is a way for the function to search through all 10 worksheets, but I am more than happy to modify the formula myself to look in each specific worksheet.

Thank you so much!!
Anna

 

[GlennUK]

I'm glad that helped. My pleasure Anna