Keeping Hyperlink Location In IF Formula

[mageeg]

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.

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?

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

 

[mageeg]

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

 

[mageeg]

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?

 

[jasonb75]

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.