Solved

Hyperlink to specific folders based on criteria in another column

Posted on 2014-02-10
9
274 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now