Excel - Hyperlink - Tying to a cell

Lately one friend asked my help to figure out the way to use hyperlink to a specific cell in Excel Sheet; Which was easy after trying 4-5 options and 2 hours of Google search. So I decided to document it for future reference!

One liner:
  The technique is 'define the name' for the cell to be linked and use that 'defined name' in hyper link.

Ref:
http://excelribbon.tips.net/T006195_Tying_a_Hyperlink_to_a_Specific_Cell.html
http://www.k2e.com/tech-update/tips/418-tip-fastest-way-to-create-defined-names-in-excel

Before going on step by step, please find what is 'Name Box'. [courtesy link from http://blog.accountants.intuit.com/ways-to-be-more-productive/low-tech-excel-tips/]

Name box:

 The left most cell in 'Formula bar' is the Name Box. When you hover the Name Box cell, it shows 'Enter a name for a cell range, or select a named range from the list' [in MSOffice 2013]

Step by Step:

Create the Defined Name:

1. Select the cell you want to be hyperlinked (to be jumped to). In the 'Name Box' [check the image above], enter a 'reference' name and press return key. [Assumed 'Reference1' for our example case]

Use the Defined Name in Hyperlink

1. Go to the cell where you want to place the hyperlink (to be jumped from), right click and click on 'Hyperlink' from the menu
2. In the 'Insert Hyperlink' popup window, in 'Anchor' ['Link to' for older version] section, click on 'locate'; which opens another popup window
3. In the 'Select Place in Document' popup, expand the 'Defined Names' by clicking the small triangle [or + icon] next to it [make the triangle point down, or change + to -, by clicking], and select the reference you made in step 2. [Our example 'Reference1']

Now your hyperlink pointed to the reference; so inserting or deleting any rows or columns won't affect your hyperlink pointing cell location.


All the best!

Java Working Directory - Wrong GDD

Lately I spend almost a day, actually a whole day to figure out jar file running directory. Actually what I needed was 'System.getProperty("user.dir")'

Ref: https://docs.oracle.com/javase/tutorial/essential/environment/sysprop.html

Here is the function first I wrote which worked across Mac, Linux and Windows 8

private static String workingDirectory() {

  File currentJavaJarFile = new File(<any>Util.class
                            .getProtectionDomain()
                            .getCodeSource()
                            .getLocation()
                            .getPath());

  String currentJavaJarFilePath = currentJavaJarFile.getAbsolutePath();

  String parentDir = currentJavaJarFile.getParent();

  String jarFilename = currentJavaJarFile.getName();

  parentDir = parentDir.toString().replace("\\", "\\\\"); //backslash directory separator error in windows

  String directory = currentJavaJarFilePath.replaceAll(parentDir.toString(), "").replace((File.separator + jarFilename), "");

  return directory;

}

Ref:http://stackoverflow.com/questions/320542/how-to-get-the-path-of-a-running-jar-file

OneJar - Cool Experience

Lately I had to build a executable jar with many other dependencies jars; Failed with 2 solutions, same as Simon Tuffs,  then I decided to use what he offered, the OneJar.

Here are the Steps used in Mac 10.10, Eclipse IDE 3.7

Ref: http://obscuredclarity.blogspot.com/2011/05/java-one-jar-example-using-ant-and.html

One time OneJar Load in IDE

1. Downloaded one-jar-ant-task-0.97.jar
2. Copied the file into /Applications/Eclipse/plugins
3. In Eclipse IDE (3.7), Preferences -> Ant -> Runtime, in right side window, click on 'Ant Home Entries (Default)'; Then use 'Add External JARs...' to add the newly added one-jar-ant-task jar file.

Ant Build Changes

NOTE: Assuming the project build is successful, and you have to create the new exec bundled onejar

Copy and paste the follow code snippet to build onejar and execute the one jar file; Dont forget to edit main jar file location and lib jar file locations to fit the main Ant build

<taskdef name="one-jar" classname="com.simontuffs.onejar.ant.OneJarTask" onerror="report" />

  <target name="onejar" depends="jar">

    <!-- Construct the One-JAR file -->

    <one-jar destfile="${ant.project.name}Exec.jar">

      <manifest>

        <attribute name="One-Jar-Main-Class" value="${main-class}"/>

      </manifest>

      <main jar="${ant.project.name}.jar" />

      <lib>

        <fileset dir="${lib.dir}" />

      </lib>
      <fileset dir="${src.dir}" includes="log4j.properties"/>

    </one-jar>

  </target>

  <target name="run onejar" depends="onejar">

    <java jar="${ant.project.name}Exec.jar" fork="true" />

  </target>



Thanks Simon Tuffs!

Ant build xml in Gist

More Example: http://one-jar.cvs.sourceforge.net/viewvc/one-jar/one-jar-log4j/

Other Ref:
http://j2stuff.blogspot.com/2011/07/onejava-example.html