Solved

# Hyperlink to specific folders based on criteria in another column

Posted on 2014-02-10

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