How can I simplify this HYPERLINK formula?

J

jmpatrick

Active Member
Joined
Aug 17, 2016
Messages
482
Office Version
  1. 365
Platform
  1. Windows
I have this formula that creates hyperlinks based on the contents in C8. This works fine but it looks rather ugly. It seems like the ))))) characters look clunky. Is there a more elegant way of doing this?

VBA Code: 
=IF(COUNTIF($C8,"*1072*"),HYPERLINK("mailto:chadd.willson@blahblahblah","Chadd Willson"),
IF(COUNTIF($C8,"*1076*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1079*"),HYPERLINK("mailto:scott.bennett@blahblahblah","Scott Bennett"),
IF(COUNTIF($C8,"*1080*"),HYPERLINK("mailto:brian.curry@blahblahblah","Brian Curry"),
IF(COUNTIF($C8,"*1081*"),HYPERLINK("mailto:kyle.trombley@blahblahblah","Kyle Trombley"),
IF(COUNTIF($C8,"*1083*"),HYPERLINK("mailto:scott.bennett@blahblahblah","Scott Bennett"),
IF(COUNTIF($C8,"*1084*"),HYPERLINK("mailto:scott.bennett@blahblahblah","Scott Bennett"),
IF(COUNTIF($C8,"*1085*"),HYPERLINK("mailto:davor.zubac@blahblahblah","Davor Zubac"),
IF(COUNTIF($C8,"*1086*"),HYPERLINK("mailto:davor.zubac@blahblahblah","Davor Zubac"),
IF(COUNTIF($C8,"*1087*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1089*"),HYPERLINK("mailto:nicholas.surma@blahblahblah","Nicholas Surma"),
IF(COUNTIF($C8,"*1090*"),HYPERLINK("mailto:kevin.demeere@blahblahblah","Kevin DeMeere"),
IF(COUNTIF($C8,"*1091*"),HYPERLINK("mailto:nicholas.surma@blahblahblah","Nicholas Surma"),
IF(COUNTIF($C8,"*1092*"),HYPERLINK("mailto:ali.taha@blahblahblah","Ali Taha"),
IF(COUNTIF($C8,"*1093*"),HYPERLINK("mailto:adam.goldberg@blahblahblah","Adam Goldberg"),
IF(COUNTIF($C8,"*1094*"),HYPERLINK("mailto:adam.goldberg@blahblahblah","Adam Goldberg"),
IF(COUNTIF($C8,"*1095*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1096*"),HYPERLINK("mailto:eric.henderson@blahblahblah","Eric Henderson"),
IF(COUNTIF($C8,"*1097*"),HYPERLINK("mailto:eric.henderson@blahblahblah","Eric Henderson"),
IF(COUNTIF($C8,"*1098M*"),HYPERLINK("mailto:nicholas.surma@blahblahblah","Nicholas Surma"),
IF(COUNTIF($C8,"*1099*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1100*"),HYPERLINK("mailto:brian.curry@blahblahblah","Brian Curry"),
IF(COUNTIF($C8,"*1101*"),HYPERLINK("mailto:wally.hedemark@blahblahblah","Wally Hedemark"),
IF(COUNTIF($C8,"*1102*"),HYPERLINK("mailto:garrett.vermeersch@blahblahblah","Garrett Vermeersch"),
IF(COUNTIF($C8,"*1103*"),HYPERLINK("mailto:jason.poag@blahblahblah","Jason Poag"),
IF(COUNTIF($C8,"*1104*"),HYPERLINK("mailto:adam.goldberg@blahblahblah","Adam Goldberg"),
IF(COUNTIF($C8,"*1105*"),HYPERLINK("mailto:davor.zubac@blahblahblah","Davor Zubac"),
IF(COUNTIF($C8,"*1106*"),HYPERLINK("mailto:timothy.clemens@blahblahblah","Tim Clemens"),
IF(COUNTIF($C8,"*1107*"),HYPERLINK("mailto:chadd.willson@blahblahblah","Chadd Willson"),
IF(COUNTIF($C8,"*1108*"),HYPERLINK("mailto:shaun.jaward@blahblahblah","Shaun Jaward"),
IF(COUNTIF($C8,"*1109M*"),HYPERLINK("mailto:shaun.jaward@blahblahblah","Shaun Jaward"),
IF(COUNTIF($C8,"*1110*"),HYPERLINK("mailto:eric.henderson@blahblahblah","Eric Henderson"),
IF(COUNTIF($C8,"*1111*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1112M*"),HYPERLINK("mailto:kyle.trombley@blahblahblah","Kyle Trombley"),
IF(COUNTIF($C8,"*1113*"),HYPERLINK("mailto:adam.goldberg@blahblahblah","Adam Goldberg"),
IF(COUNTIF($C8,"*1114M*"),HYPERLINK("mailto:eric.henderson@blahblahblah","Eric Henderson"),
IF(COUNTIF($C8,"*1115M*"),HYPERLINK("mailto:scott.bennett@blahblahblah","Scott Bennett"),
IF(COUNTIF($C8,"*1116*"),HYPERLINK("mailto:kirk.peffers@blahblahblah","Kirk Peffers"),
IF(COUNTIF($C8,"*1117*"),HYPERLINK("mailto:kyle.trombley@blahblahblah","Kyle Trombley"),
IF(COUNTIF($C8,"*1118*"),HYPERLINK("mailto:chadd.willson@blahblahblah","Chadd Willson"),
IF(COUNTIF($C8,"*1119*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1120*"),HYPERLINK("mailto:davor.zubac@blahblahblah","Davor Zubac"),
IF(COUNTIF($C8,"*1121*"),HYPERLINK("mailto:jamie.celestini@blahblahblah","Jamie Celestini"),
IF(COUNTIF($C8,"*1122M*"),HYPERLINK("mailto:timothy.clemens@blahblahblah","Tim Clemens"),
IF(COUNTIF($C8,"*1123*"),HYPERLINK("mailto:michael.zaski@blahblahblah","Mike Zaski"),
IF(COUNTIF($C8,"*1124*"),HYPERLINK("mailto:shaun.jaward@blahblahblah","Shaun Jaward"),
IF(COUNTIF($C8,"*1125*"),HYPERLINK("mailto:shaun.jaward@blahblahblah","Shaun Jaward"),
IF(COUNTIF($C8,"*1126*"),HYPERLINK("mailto:Jacob.Johnson@blahblahblah","Jacob Johnson"),
IF(COUNTIF($C8,"*1127*"),HYPERLINK("mailto:michael.mcgraw@blahblahblah","Mike McGraw"),
IF(COUNTIF($C8,"*1128*"),HYPERLINK("mailto:jamie.celestini@blahblahblah","Jamie Celestini"),
"Z"))))))))))))))))))))))))))))))))))))))))))))))))))
 
Sort by date Sort by votes
Toadstool said:
How close am I?
Click to expand...
Very! The first issue is it won't find partial matches. It will match 1119, not 1119-8.

Last thing, when I copy the formula down to the rest of the rows they are all identical. For instance, I'm expecting to see $C$10 in row row 10 and $C$11 in row 11.

Here's a couple screen shots...

This is the main sheet (Calendar)...

excel1.jpg


This is the sheet with the data (FieldManagers)...

excel2.jpg


Using your suggestion, my code looks like this:

VBA Code: 
=HYPERLINK(INDEX(FieldManagers!D:D,MATCH(Calendar!$C$10,FieldManagers!$B:$B,0)),INDEX(FieldManagers!C:C,MATCH(Calendar!$C$10,FieldManagers!$B:$B,0)))

The #N/A is because it won't do a partial match. If C10 is 1119 it works.
 
Last edited:
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
jmpatrick said:
Very! The first issue is it won't find partial matches. It will match 1119, not 1119-8.
Click to expand...

How about

JMPatrick-V2.xlsx
ABCD
1Subdivision NameCodeNameEmail
2ABC1072Chadd Willsonmailto:chadd.willson@blahblahblah
3DEF1076Mike Zaskimailto:michael.zaski@blahblahblah
4FGX1119-8Scott Bennettmailto:scott.bennett@blahblahblah
5KWP1080Brian Currymailto:brian.curry@blahblahblah
6LKW1081Kyle Trombleymailto:kyle.trombley@blahblahblah
7WKS1083Scott Bennettmailto:scott.bennett@blahblahblah
FieldManagers


JMPatrick-V2.xlsx
CDE
1Sub # / Lot #Subdivision NameField Manager
21076Mike Zaski
31119-8Scott Bennett
41080Brian Curry
51096Eric Henderson
61097Eric Henderson
71098MNicholas Surma
81099Mike Zaski
91108Shaun Jaward
Calendar
Cell Formulas
RangeFormula
E2:E9E2=HYPERLINK(INDEX(FieldManagers!D:D,MATCH(Calendar!$C2,FieldManagers!$B:$B,0)),INDEX(FieldManagers!C:C,MATCH(Calendar!$C2,FieldManagers!$B:$B,0)))
 
Upvote 0
Toadstool said:
How about

JMPatrick-V2.xlsx
ABCD
1Subdivision NameCodeNameEmail
2ABC1072Chadd Willsonmailto:chadd.willson@blahblahblah
3DEF1076Mike Zaskimailto:michael.zaski@blahblahblah
4FGX1119-8Scott Bennettmailto:scott.bennett@blahblahblah
5KWP1080Brian Currymailto:brian.curry@blahblahblah
6LKW1081Kyle Trombleymailto:kyle.trombley@blahblahblah
7WKS1083Scott Bennettmailto:scott.bennett@blahblahblah
FieldManagers


JMPatrick-V2.xlsx
CDE
1Sub # / Lot #Subdivision NameField Manager
21076Mike Zaski
31119-8Scott Bennett
41080Brian Curry
51096Eric Henderson
61097Eric Henderson
71098MNicholas Surma
81099Mike Zaski
91108Shaun Jaward
Calendar
Cell Formulas
RangeFormula
E2:E9E2=HYPERLINK(INDEX(FieldManagers!D:D,MATCH(Calendar!$C2,FieldManagers!$B:$B,0)),INDEX(FieldManagers!C:C,MATCH(Calendar!$C2,FieldManagers!$B:$B,0)))
Click to expand...

That works, but only if the sub code matches exactly, like 1119. If the sub code is 1119-20 (or 1119M-19) it gives me a #N/A.
 
Upvote 0
You're talking of the sub code on Calendar being 1119-20? So it shouldn't look for 1119-20 on Field Managers, just 1119?

Then we can take the "-nn" suffix off the search. The Field Managers tab is unchanged. Hre's the new Calendar sheet.

JMPatrick-V3.xlsx
CDE
1Sub # / Lot #Subdivision NameField Manager
21076-10Mike Zaski
31119-8Mike Zaski
41080-10Brian Curry
51080-44Brian Curry
61080Brian Curry
71098M-19Nicholas Surma
81099Mike Zaski
91108Shaun Jaward
101109MShaun Jaward
111110Eric Henderson
121111Mike Zaski
Calendar
Cell Formulas
RangeFormula
E2:E12E2=HYPERLINK(INDEX(FieldManagers!D:D,MATCH(LEFT(Calendar!$C2,IFERROR(FIND("-",Calendar!$C2)-1,99)),FieldManagers!$B:$B,0)),INDEX(FieldManagers!C:C,MATCH(LEFT(C2,IFERROR(FIND("-",$C2)-1,99)),FieldManagers!$B:$B,0)))
 
Upvote 0
Solution
Thanks. I'll try it in the morning. I'm all hopped up on Mexican Coke right now.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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