Solved

# Automatically hyperlinking to specific folder based on value entered in cell

Posted on 2014-03-22

The following formula currently resides in Column G:

=IFERROR(HYPERLINK("Q:\20"&LEFT(F78,2)&"\"&IF(MID(F78,FIND("-",F78)+1,1)="9","DND\","")&TEXT(F78,"0000-00"),""&TEXT(F78,"0000-00")),"")

The formula references a numeric value in Column F, which corresponds to a folder name on the Q network drive. It takes the number in Column F, and creates a hyperlink to the correct network folder on Q based on the criteria within the formula (this criteria is outlined at the end of my question below), and autopopulates the hyperlinked folder number in Column G.

The numeric value in column F is always formatted as follows:

yy-xxxx where yy is the last 2 digits of a year, followed by a dash, followed by a 4-digit number.

Instead of having 2 columns with the same data, Column F with the un-hyperlinked folder number, and Column G with the hyperlinked folder number, I would like to:

1. Eliminate Column F

2. Have the user enter the folder number (yy-xxxx) into Column G, upon which the hyperlink would automatically be created, based on the same criteria as the current formula above (and outlined in detail below).

Outline of criteria contained in the current formula:

The 3 sub folders on the Q network drive that could contain the folder number entered by the user into Column G:

Q:\2013\DND

Q:\2014

Q:\2014\DND

All sub folders in the above folders are formatted the same way as described above: "yy-####".

If value entered into Column G is:

13-1###

13-2###

13-3###

13-4###

13-5###

13-6###

13-7###

13-8###

then the value needs to hyperlink to corresponding folder number within the 'Q:\2013' sub folder.

If value entered into Column G is:

13-9###

then the value needs to hyperlink to corresponding folder number within the 'Q:\2013\DND' sub folder:

If value entered into Column G is:

14-1###

14-2###

14-3###

14-4###

14-5###

14-6###

14-7###

14-8###

then the value needs to hyperlink to corresponding folder number within the 'Q:\2014' sub folder.

If value entered into Column G is:

14-9###

then the value needs to hyperlink to corresponding folder number within the 'Q:\2014\DND' sub folder:

Thanks,

Andrea