Solved

Hyperlink to specific folders based on criteria in another column

Posted on 2014-02-10
9
276 Views
Last Modified: 2014-02-11
Currently, Column F contains values formatted as follows:
13-### (ie., 13-4444)
This value, ie., '13-4444', corresponds to a specific sub folder on a network drive under a 'Q:\2013' folder.
Column G contains a formula that creates a hyperlink to that folder, based on the value entered in Column F:
=HYPERLINK("Q:\2013\"&TEXT(F2,"0000-00"),""&TEXT(F2,"0000-00"))

As of January 1, there are additional folders to be factored into the original hyperlink formula:
Q:\2013\DND
Q:\2014
Q:\2014\DND
All sub folders in the above new folders are formatted the same way as the original Q:|2013 folder (yy-####).

So now we need the hyperlink formula in Column G to be updated to hyperlink to the correct sub folder based on the following criteria outlined below:

If Column F's value is:
13-1###
13-2###
13-3###
13-4###
13-5###
13-6###
13-7###
13-8###
then the formula in Column G needs to link to the 'Q:\2013' folder:
=HYPERLINK("Q:\2013\"&TEXT(F2,"0000-00"),""&TEXT(F2,"0000-00"))

If Column F's value is:
13-9###
then the formula in Column G needs to link to the 'Q:\2013\DND' folder:
=HYPERLINK("Q:\2013\DND\"&TEXT(F22,"0000-00"),""&TEXT(F22,"0000-00"))

If Column F's value is:
14-1###
14-2###
14-3###
14-4###
14-5###
14-6###
14-7###
14-8###
then the formula in Column G needs to link to the 'Q:\2014' folder:
=HYPERLINK("Q:\2014\"&TEXT(F2,"0000-00"),""&TEXT(F2,"0000-00"))

If Column F's value is:
14-9###
then the formula in Column G needs to link to the 'Q:\2014\DND' folder:
=HYPERLINK("Q:\2014\DND\"&TEXT(F22,"0000-00"),""&TEXT(F22,"0000-00"))

I think that covers it...thanks in advance for assistance on this.

Andrea
0
Comment
Question by:Andreamary
  • 4
  • 3
  • 2
9 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39847807
Try:

=HYPERLINK("Q:\20"&LEFT(F2,2)&"\"&IF(MID(F2,FIND("-",F2)+1,1)="9","DND","")&TEXT(F2,"0000-00"),""&TEXT(F2,"0000-00"))

This also selects the correct year so that you don't need to have a separate formula for 2013 and 2014.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39847816
Hi,

pls try

=HYPERLINK("Q:\20"&LEFT(F2,2)&IF(MID(F2,4,1)="9","\DND","")&"\"&TEXT(F2,"0000-00"),""&TEXT(F2,"0000-00"))

EDIT

@NBVC you forgot the Backslash after DND

Regards
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39847856
Thanks Rgonzo...

that would be:

=HYPERLINK("Q:\20"&LEFT(F2,2)&"\"&IF(MID(F2,FIND("-",F2)+1,1)="9","DND","")&"\"&TEXT(F2,"0000-00"),""&TEXT(F2,"0000-00"))
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39847884
@NBVC Sorry wrong once more. If the DND is not found you will have 2 Backslashes

Regards
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39848024
Lol...

3rd time's a charm?

=HYPERLINK("Q:\20"&LEFT(F2,2)&"\"&IF(MID(F2,FIND("-",F2)+1,1)="9","DND\","")&TEXT(F2,"0000-00"),""&TEXT(F2,"0000-00"))
0
 

Author Comment

by:Andreamary
ID: 39848045
Thanks very much! I used the revised formula provided and it seems to work very well. The only issue is that column G shows "#VALUE!" now if column F is blank. My preference is that the cell in column G is blank under these circumstances. BTW, I wasn't sure if the comment immediately above impacts the proposed formula?

Andrea
0
 

Author Comment

by:Andreamary
ID: 39848209
I've updated the formula to the very last one provided, thanks, so if there's a way to suppress the "#VALUE!" (described above) that would be perfect.

Andrea
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39848225
Try either:

=IF(F2="","",HYPERLINK("Q:\20"&LEFT(F2,2)&"\"&IF(MID(F2,FIND("-",F2)+1,1)="9","DND\","")&TEXT(F2,"0000-00"),""&TEXT(F2,"0000-00")) )

or

=IFERROR(HYPERLINK("Q:\20"&LEFT(F2,2)&"\"&IF(MID(F2,FIND("-",F2)+1,1)="9","DND\","")&TEXT(F2,"0000-00"),""&TEXT(F2,"0000-00")) ,"")
0
 

Author Closing Comment

by:Andreamary
ID: 39850344
Terrific...very pleased! Thank you...
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question