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