Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Conditional Hyperlinks with Multiple IFS

0

Hello, I need a little guidance on how to make hyperlinks work for a scenario.  Attached example file shows the basics that I got to work.  With the below formula, I am able to have the "View Layout" pop up as a proper hyperlink IF cell D12 = 2 and D13 = 1.  Where I'm struggling is with multiple IFs or conditions added....I need a way to change the hyperlink URL based on the values in D12 and D13.  So if D12 changes to 11 and D13 stays at 1, "View Layout" still appears, but the underlying hyperlink changes.

=IF(AND(D12=2,D13=1),HYPERLINK("https://test1.jpg","View Layout"),"")

Do I use the IFS instead of "IF" function or is there an easier way to do this?  I plan on having many (as much as 30+ combinations related to the values in D12 to D13) and the hyperlinks will go to images.

Thanks,

Kyle

excel
posted by: SmittyHPC (rep: 4) on Thu Jun 3, 2021 at 9:11 am
Answer
Discuss

Answers

0
Selected Answer

If you want to have many combinations the link's destination must be retrieved from  a list. Therefore the use of IF or IFS of any kind must be exxcluded from the start. You need a row number. With that you can retrieve any URL from a simple table with 2 columns - ID# and URL.

You might find the number from a matrix with the values of D3:D7 in rows and the values of D12:D13 in columns. The row number is found with VLOOKUP, XLOOKUP or INDEX/MATCH at the intersection of rows and columns. You might use a formula like the one below where the lookup value "10" is itself retrieved by a function as just described.

=HYPERLINK(VLOOKUP(10,Sheet1!A:B,2,FALSE),"View Layout")
posted by: Variatus (rep: 4889) on Thu Jun 3, 2021 at 7:25 pm
Discuss

Discussion

Thanks!
SmittyHPC (rep: 4) Jun 4, '21 at 7:17 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login