Create hyperlink based on 2 conditions

Namratashadangi

New Member
Joined
Jan 25, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I want to create a hyperlink in sheet1 which will point to corresponding row in sheet2 based upon below condition.
1. If A2 of sheet1=ST then lookup B2 of sheet1 to A column of sheet2 and w1 of sheet1 to C column of sheet2.if match found create hyperlink to the corresponding row in sheet2.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi Namratashadangi,

This should do what you ask:

Namratashadangi.xlsx
AB
1
2
3
4Dog
5
6
Sheet2


Namratashadangi.xlsx
ABC
1
2STDogLink
3
Sheet1
Cell Formulas
RangeFormula
C2C2=IF(A2<>"ST","",HYPERLINK("#Sheet2!A"&MATCH(B2,Sheet2!A:A,0),"Link"))
 
Upvote 0
Here I have a top row with date and I want that when I click on the dog link it should go to dog in sheet2 with the respective date. In sheet2 there are 2 dogs with ST but different dates.i am not able to upload my sheet here.not sure if I am able to explain.
You can email to me at namrata.shadangi@gmail.com and I will send you my sheet.
 
Upvote 0
I missed a check

Namratashadangi.xlsx
ABC
1AnimalDate
2Cat01-Jan-22
3Sheep02-Feb-22
4Dog01-Jan-22
5Goat02-Feb-22
6Sheep01-Jan-22
7Dog06-Jun-22
8
Sheet2


Namratashadangi.xlsx
ABCDW
101-Jan-22
2STDogLink
3ZZDog 
4XXDog 
5STSheepLink
6STGoat 
7ZZSheep 
8STCatLink
9 
Sheet1
Cell Formulas
RangeFormula
C2:C9C2=IFERROR(HYPERLINK("#Sheet2!A"&AGGREGATE(15,6,ROW(Sheet2!$C$2:$C$9999)/((Sheet2!$C$2:$C$9999=$W$1)*($A2="ST")*(Sheet2!$A$2:$A$9999=B2)),1),"Link"),"")
 
Upvote 0
Thank you So much. It's working. Would you mind explaining the given logic. I am not able to understand what aggregate function is doing here.
 
Upvote 0
You're welcome.

The AGGREGATE(15,6,........,1) is using the 15 option (SMALL) to find the nth entry (1 denotes 1st only) and the 6 tells it to ignore errors while searching.

Normally the next parameter after the 15,6 would be a range of cells which contain the numbers for it to find the nth entry but here we're just giving it ROW numbers from 2 to 9999
ROW(Sheet2!$C$2:$C$9999)
It's just using the row numbers so the range would also work if you changed to $ZZ2:$ZZ$9999

Next it divides each row number (/) by the logical comparison
((Sheet2!$C$2:$C$9999=$W$1)*($A2="ST")*(Sheet2!$A$2:$A$9999=B2))
So if any of the three comparisons is FALSE (logical zero) then the result will be zero (1*1*0=0, 0*0*0=0, etc) which is divided into the row number, it generates a#DIV/0 which the AGGREGATE option 6 traps as an error so it says "Ignore errors, so let's try the next row".
When it finds a comparison where all conditions are TRUE, a logical 1, then that 1 is divided into the row number and it returns that row number to the HYPERLINK.
 
Upvote 0
Solution
Hello,
Can you please help me with an additional feature on this existing formula.
I want to go to corresponding row (A:C) when I click on the link i.e. when I click on the dog link in sheet1 it should High light the A4:C4 in sheet2 instead of just A2.
 
Upvote 0
Hello,
Can you please help me with an additional feature on this existing formula.
I want to go to corresponding row (A:C) when I click on the link i.e. when I click on the dog link in sheet1 it should High light the A4:C4 in sheet2 instead of just A4.
 
Upvote 0
Then you'll need to change the target from "A4" to "A4:C4" so you'll need to run the AGGREGATE twice.

Namratashadangi.xlsx
ABCDW
101-Jan-22
2STDogLink
3ZZDog 
4XXDog 
5STSheepLink
6STGoat 
7ZZSheep 
8STCatLink
9 
2nd
Cell Formulas
RangeFormula
C2:C9C2=IFERROR(HYPERLINK("#Sheet2!A"&AGGREGATE(15,6,ROW(Sheet2!$C$2:$C$9999)/((Sheet2!$C$2:$C$9999=$W$1)*($A2="ST")*(Sheet2!$A$2:$A$9999=B2)),1)&":C"&AGGREGATE(15,6,ROW(Sheet2!$C$2:$C$9999)/((Sheet2!$C$2:$C$9999=$W$1)*($A2="ST")*(Sheet2!$A$2:$A$9999=B2)),1),"Link"),"")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top