asked on

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

NBVC

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.

Rgonzo1971

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

NBVC

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"))

Rgonzo1971

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

Regards

NBVC

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"))

Andreamary

ASKER

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

Andreamary

ASKER

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

ASKER CERTIFIED SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

Terrific...very pleased! Thank you...