Make Dynamic Hyperlink .

[avisoft20]

Good Evening All,
I have two sheet i.e Sheet-1 and Sheet-2,Sheet-1 contains "State" Sheet-2 contains four columns ,on sheet-1 when i click on state name that time second sheet-2 automatic open .

For Example-
Sheet-1.

Sr.No	State
1	Delhi
2	Punjab
3	Uttar Pradesh

<tbody>
</tbody>

Sheet-2.

Sr.no	State	Town	Pin
1	Delhi	Karolbagh	110092
2	Delhi	Uttam Nagar	110093
3	Punjab	Amritsar	210032
4	Punjab	Ludhiana	210034
5	Punjab	Faridkot	210035
6	Uttar Pradesh	Kanpur	220056
7	Uttar Pradesh	Lucknow	220057
8	Uttar Pradesh	Varanasi	220058
9	Uttar Pradesh	Agra	220059
10	Uttar Pradesh	Meruth	220059
11	Uttar Pradesh	Mathura	220060

<tbody>
</tbody>

if i click on Delhi State on Sheet-1.
And ans will be.

Sr.no	State	Town	Pin
1	Delhi	Karolbagh	110092
2	Delhi	Uttam Nagar	110093

<tbody>
</tbody>

 

[BQardi]

Something like this (no VBA required)...
Assuming the tables start in A1, in C2 insert this formula and drag down:
(Change "ThisWorkbooksName.xlsx" to the name of your workbook)

=HYPERLINK("[[COLOR=#ff0000]ThisWorkbooksName.xlsx[/COLOR]]Sheet2!A"&MATCH(B2,'Sheet2'!B:B,0)&":D"&COUNTIF('Sheet2'!B:B,B2)+MATCH(B2,'Sheet2'!B:B,0)-1, B2)

This will create a hyperlink where, when clicked, it will take you to Sheet2 and select the range for the specific name.

If you have more specific needs, a VBA solution is probably what you are after...
Let's know...

 

[avisoft20]

Thank You so much Mr. BQardi.