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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Maybe something on the lines of
Excel Formula:
=HYPERLINK(IF(AND(I2>=2200487,I2<=2200546),"C:\Some Folder\"&I2&".pdf",""))
 
Upvote 0
Maybe something on the lines of
Excel Formula:
=HYPERLINK(IF(AND(I2>=2200487,I2<=2200546),"C:\Some Folder\"&I2&".pdf",""))
That looks like it should work, but unfortunately getting no luck. It replaces the certificate name with the destination folder and still cannot open.

Unless i have messed it up somehow!

VBA Code:
=HYPERLINK(IF(AND(I1>=2200487,I2<=2200546),"T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&I2&".pdf",""))
 
Upvote 0
That looks like it should work, but unfortunately getting no luck. It replaces the certificate name with the destination folder and still cannot open.

Unless i have messed it up somehow!

VBA Code:
=HYPERLINK(IF(AND(I1>=2200487,I2<=2200546),"T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&I2&".pdf",""))

whoops, i was being stupid! got it now.

Thanks!

How would i edit this further to include the rest of the PDFs,

E.g. if L2 is >= 2200487 but <=2200546, then use H2
OR if L2 is >= 2256546 but <=2256605 then use H3
OR if L2 is >= 2278179 but <=2278238 then use H4

Etc.
 

Attachments

  • 1625827149703.png
    1625827149703.png
    23.1 KB · Views: 4
Last edited:
Upvote 0
Is H2 the name of the file then? I was looking at it that it might be a folder that contains all of those individual files.

Personally, I would list the first and last in 2 separate columns in order to use a lookup function, then join them together in the hyperlink formula as shown below. Note that there are no hyperlinks in column H or I.

Book1
HIJK
22200487220054622004902200487 - 2200546.pdf
322565462256605
422781792278238
522282232228282
622782392278248
Sheet1
Cell Formulas
RangeFormula
K2K2=IF(LOOKUP(J2,H2:H6,I2:I6)>=J2,HYPERLINK("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&LOOKUP(J2,H2:H6)&" - "&LOOKUP(J2,H2:H6,I2:I6)&".pdf",LOOKUP(J2,H2:H6)&" - "&LOOKUP(J2,H2:H6,I2:I6)&".pdf"),"")
 
Upvote 0
Is H2 the name of the file then? I was looking at it that it might be a folder that contains all of those individual files.

Personally, I would list the first and last in 2 separate columns in order to use a lookup function, then join them together in the hyperlink formula as shown below. Note that there are no hyperlinks in column H or I.

Book1
HIJK
22200487220054622004902200487 - 2200546.pdf
322565462256605
422781792278238
522282232228282
622782392278248
Sheet1
Cell Formulas
RangeFormula
K2K2=IF(LOOKUP(J2,H2:H6,I2:I6)>=J2,HYPERLINK("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&LOOKUP(J2,H2:H6)&" - "&LOOKUP(J2,H2:H6,I2:I6)&".pdf",LOOKUP(J2,H2:H6)&" - "&LOOKUP(J2,H2:H6,I2:I6)&".pdf"),"")
Hi yes sorry, the long ones e.g. 2200487 - 2200546.pdf is the name of a file.

Unfortunately i have tried the above but i am getting the #N/A error

1626077293214.png
 
Upvote 0
Noting the little triangles in the top left corners of some of the cells, are those numbers in a text format? If they are then that would explain the error.
Numbers formatted as text have different (higher) values than properly formatted numbers so are not directly comparable in most formulas.

If that is the case then this might cure it (not tested) but it would be better if the source was formatted correcty.
Excel Formula:
=IF(LOOKUP(--J2,--H2:H6,--I2:I6)>=--J2,HYPERLINK("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&LOOKUP(--J2,--H2:H6)&" - "&LOOKUP(--J2,--H2:H6,--I2:I6)&".pdf",LOOKUP(--J2,--H2:H6)&" - "&LOOKUP(--J2,--H2:H6,--I2:I6)&".pdf"),"")
 
Upvote 0
Noting the little triangles in the top left corners of some of the cells, are those numbers in a text format? If they are then that would explain the error.
Numbers formatted as text have different (higher) values than properly formatted numbers so are not directly comparable in most formulas.

If that is the case then this might cure it (not tested) but it would be better if the source was formatted correcty.
Excel Formula:
=IF(LOOKUP(--J2,--H2:H6,--I2:I6)>=--J2,HYPERLINK("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&LOOKUP(--J2,--H2:H6)&" - "&LOOKUP(--J2,--H2:H6,--I2:I6)&".pdf",LOOKUP(--J2,--H2:H6)&" - "&LOOKUP(--J2,--H2:H6,--I2:I6)&".pdf"),"")
That works, thank you!

I don't know whether you'll be able to share some insight into this or whether i'll need to start another thread, but i'll give it a go.

Now i've got this formula working on this sheet, I'm wondering if i'll be able to get this running alongside my sheet "Database"

This database sheet works through a user entry form, as you can see from the following screenshot the Serial number spoken about above is entered in Column G

1626079728747.png


I would like the correct certificate to be put into column S, depending on the Serial number entered in column G.


1626079900457.png


Most of the data in this table is through a user entry form -> Vlookup. But i've realised that's hard to do with hyperlinks, so i resorted to try formulas like the one we discussed above.

Is there anyway i can further what we've done to make the correct Certificate appear in Column S depending on the Serial number entered in column G?
 
Upvote 0
I'm not sure that I'm following correctly, if the certificate number for column S is the the same result from the earlier formula then you should be able to use the same formula by adjusting the ranges used.

The formula would work across 2 sheets the same as it works in 1 so you could have the lookup list (columns H and I in my example) in a hidden sheet for the formula to reference.

If I'm way off track then it might be better to start a new thread for this part, with my schedule for the week my reply time is likely to be quite slow, others might be able to resolve it sooner for you.
 
Upvote 0
I'm not sure that I'm following correctly, if the certificate number for column S is the the same result from the earlier formula then you should be able to use the same formula by adjusting the ranges used.

The formula would work across 2 sheets the same as it works in 1 so you could have the lookup list (columns H and I in my example) in a hidden sheet for the formula to reference.

If I'm way off track then it might be better to start a new thread for this part, with my schedule for the week my reply time is likely to be quite slow, others might be able to resolve it sooner for you.

I think you're right, and i should be able to do that!

I have attempted this however the cell remains blank after entering the formula so i must have done something wrong?

I have looked through and have checked but can't seem to find what's wrong.

you can see from this table that the columns i want from this sheet is A and B.

1626082567931.png


And the serial number in question is entered in column G, so i edit the formula to the following:
VBA Code:
=IF(LOOKUP(--G2,--ReceiverData!A2:A6, --ReceiverData!B2:B6)>=--G2,HYPERLINK("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Receiever Certs\"&LOOKUP(--G2,--ReceiverData!A2:A6)&" - "&LOOKUP(--G2,--ReceiverData!A2:A6,--ReceiverData!B2:B6)&".pdf",LOOKUP(--G2,--ReceiverData!A2:A6)&" - "&LOOKUP(--G2,--ReceiverData!A2:A6,--ReceiverData!B2:B6)&".pdf"),"")

However after entering this, the field remains blank
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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