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,""))

 

[jasonb75]

Something that I missed before was the order of the list. For the formula to work, the table needs to be in ascending order sorted by the first column. With the formatting issues from earlier this might not work too well.

There are other ways depending on the version of excel that you are working with but as I said earlier, it would be preferable to fix the table rather than trying to work around it.

 

[mageeg]

Something that I missed before was the order of the list. For the formula to work, the table needs to be in ascending order sorted by the first column. With the formatting issues from earlier this might not work too well.

There are other ways depending on the version of excel that you are working with but as I said earlier, it would be preferable to fix the table rather than trying to work around it.

Hmm i see what you mean. But it works well on the one sheet, surely this wouldn't effect it when the only thing changing is having over two sheets instead of one? (I could be wrong i'm new to this!)

The issue is I have another piece of code which takes these values and automatically creates a word template, which requires these values to be stored as text.

So i am conflicted

 

[jasonb75]

It would depend on the number that is being looked up, in the example it would only be numbers in the 2228223-2228282 range that would be problematic (because there are higher numbers above it). The other ranges should all work as expected. With a bigger list of ranges there could be other problems.

Which version of excel are you using? If office 365, does

=LET(test,"This works",test)

give you an error?

 

[mageeg]

It would depend on the number that is being looked up, in the example it would only be numbers in the 2228223-2228282 range that would be problematic (because there are higher numbers above it). The other ranges should all work as expected. With a bigger list of ranges there could be other problems.

Which version of excel are you using? If office 365, does

=LET(test,"This works",test)

give you an error?

It does not work,

I am just on Microsoft Office Standard 2013

 

[jasonb75]

That function is only in 365 at the moment, it will likely be in the next version of excel if one is released (excel 2022 if the 3 year interval continues) but there would not be an update released to add it to older versions.

I'll have a look and see what I can to to the formula to make it work as you need with that version but I may not be able to do it today.

 

[mageeg]

That function is only in 365 at the moment, it will likely be in the next version of excel if one is released (excel 2022 if the 3 year interval continues) but there would not be an update released to add it to older versions.

I'll have a look and see what I can to to the formula to make it work as you need with that version but I may not be able to do it today.

Thank you so much for your help.

The issue i may have is that there will be others using this "database" too once it is up and running. And it is possible that they will have completely different versions! So if it's not possible to do this method for all versions, i might have to try and find a new solution

I appreciate your help

 

[jasonb75]

That shouldn't be a problem, functions are never removed from excel so a formula for an old version will always work in a newer version. The problems occur when a formula is written for a newer version and then it is used in an older version that came out before one of the functions that was used.

With what I have in mind, there should be no problems in any version from 2010 onward.

 

[mageeg]

That shouldn't be a problem, functions are never removed from excel so a formula for an old version will always work in a newer version. The problems occur when a formula is written for a newer version and then it is used in an older version that came out before one of the functions that was used.

With what I have in mind, there should be no problems in any version from 2010 onward.

That sounds great thank you.

 

[jasonb75]

I haven't had chance to test this, but think that it should work. The functions used are good for any version of excel from 2007 onward.

=IFERROR(HYPERLINK("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&LOOKUP(2,1/((--G2)>=(--ReceiverData!A2:A6))/((--G2)<=( --ReceiverData!B2:B6)),ReceiverData!A2:A6)&" - "&LOOKUP(2,1/((--G2)>=(--ReceiverData!A2:A6))/((--G2)<=( --ReceiverData!B2:B6)),ReceiverData!B2:B6)&".pdf",LOOKUP(2,1/((--G2)>=(--ReceiverData!A2:A6))/((--G2)<=( --ReceiverData!B2:B6)),ReceiverData!A2:A6)&" - "&LOOKUP(2,1/((--G2)>=(--ReceiverData!A2:A6))/((--G2)<=( --ReceiverData!B2:B6)),ReceiverData!B2:B6)&".pdf"),"")

If it doesn't work as expected, try selecting the formula cell, press the f2 key, then press Ctrl Shift and Enter together to array confirm the formula. I don't think that this will be needed but I but without testing I'm not 100% sure.

 

[mageeg]

I haven't had chance to test this, but think that it should work. The functions used are good for any version of excel from 2007 onward.

=IFERROR(HYPERLINK("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&LOOKUP(2,1/((--G2)>=(--ReceiverData!A2:A6))/((--G2)<=( --ReceiverData!B2:B6)),ReceiverData!A2:A6)&" - "&LOOKUP(2,1/((--G2)>=(--ReceiverData!A2:A6))/((--G2)<=( --ReceiverData!B2:B6)),ReceiverData!B2:B6)&".pdf",LOOKUP(2,1/((--G2)>=(--ReceiverData!A2:A6))/((--G2)<=( --ReceiverData!B2:B6)),ReceiverData!A2:A6)&" - "&LOOKUP(2,1/((--G2)>=(--ReceiverData!A2:A6))/((--G2)<=( --ReceiverData!B2:B6)),ReceiverData!B2:B6)&".pdf"),"")

If it doesn't work as expected, try selecting the formula cell, press the f2 key, then press Ctrl Shift and Enter together to array confirm the formula. I don't think that this will be needed but I but without testing I'm not 100% sure.

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?