Solved

Hyperlink to specific folders based on criteria in another column

Posted on 2014-02-10
9
273 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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Expert Comment

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

Regards
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 23

Expert Comment

by:NBVC
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Terrific...very pleased! Thank you...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

11 Experts available now in Live!

Get 1:1 Help Now