Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Hyperlink to specific folders based on criteria in another column

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
Andreamary
Asked:
Andreamary
  • 4
  • 3
  • 2
1 Solution
 
NBVCCommented:
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
 
Rgonzo1971Commented:
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
 
NBVCCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rgonzo1971Commented:
@NBVC Sorry wrong once more. If the DND is not found you will have 2 Backslashes

Regards
0
 
NBVCCommented:
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
 
AndreamaryAuthor Commented:
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
 
AndreamaryAuthor Commented:
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
 
NBVCCommented:
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
 
AndreamaryAuthor Commented:
Terrific...very pleased! Thank you...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now