Keeping Hyperlink Location In IF Formula

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
I am banging my head against the table with this one, i feel like it must be fairly simple but can't seem to figure it out.

I have a list of PDF's in column H, with hyperlinks to open each one. However as you can see below, the name is a range of numbers.

1625759374466.png


I then have Column I, which i want to enter a number. I want a formula that if number entered is between the range of the numbers in the PDF file name, then display PDF link.

At the moment i have this formula (see below), which does what i ask except obviously it doesn't allow you to open the hyperlink as it doesn't know where to go. How can i automatically do this?

VBA Code:
=HYPERLINK(IF(AND(I2>=2200487,I2<=2200546),H2,""))

1625759510891.png
 

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
Thank you, this works!

However one question I have is,

I need to paste this into multiple rows in the database sheet,

doing this changes the lookup of A and B + 1 everytime. e.g. the first column is A2:A6 to B2:B6 which is how it should be, but then pasting this in the second row changes it to A7, B7.

Is there a way to stop this?
Nevermind,

i've sorted it! sorry for the noob question..

thank you so much for your help
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
Nevermind,

i've sorted it! sorry for the noob question..

thank you so much for your help

sorry one final question,

If i was to add another row this To and From table. e.g. add another row in A7 and B7. Would i manually have to change the formula to A2:A7, B2:B7, or is there a way to automate this somehow?

1626160905808.png
 

Attachments

  • 1626160875544.png
    1626160875544.png
    5.3 KB · Views: 0

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,900
Office Version
  1. 365
Platform
  1. Windows
If that is a structured table (applied from the Insert tab on the excel ribbon) then you could use the table names instead of normal references, otherwise the easiest way is to set up dynamic named ranges for each of the columns in the table.
 

Forum statistics

Threads
1,144,342
Messages
5,723,813
Members
422,518
Latest member
quack_quack

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
Top