Projects Possible

on the customer name, selected HYPERLINK and from the Hyperlink dialog I told it I wanted to link to a “Place in this Document” (see image) and then I selected the correct INFOXX worksheet for that customer name. I did the same thing for the customer number only now I wanted to link to SHEETXX for that customer. I could now quickly get to both worksheets of customer information without having to try to figure out which one it was… and to make getting back to my Selection worksheet easier I just but a hyperlink on each INFOXX and SHEETXX with the word “BACK” to bring me back to the selection worskeet that I called CustomerSummary.

=IF(B20=”",”",HYPERLINK(CONCATENATE(”[WorksheetName.xls]Sheet”,VALUE(B20),”!A1″),”?”))

Let’s break this down…

IF(B20=”",”",HYPERLINK…) - This is saying that if there isn’t a value in the B20 cell (in other words there are no SHEETXX worksheet then don’t put anything in the hyperlink just leave the cell blank. It is always a good idea to do some kind of a check like this so that you don’t end up with errors. [Keep in mind that if you put a number into B20 and there is no corresponding SHEETXX you will get a POPUP error in Excel telling you there is no reference.]

CONCATENATE(”[WorksheetName.xls]Sheet”,VALUE(B20),”!A1″) is telling Excel to combine together the text value of [WorksheetName.xls]Sheet, with the text value of B20 converted to a number, with the text value of !A1 resulting in a final text value of [WorksheetName.xls]Sheet5!A1 (assuming that B20 has a value of 5 in it).

The HYPERLINK formula then uses the newly created text value and attaches it to that funny “sun” type image so that on my spreadsheet the only thing that appears is the “sun”. When I click on the “sun” I am taken to cell A1 of the worksheet titled SHEETXX where the XX is equal to the value that is found in Cell B20.

If I copy this formula down the page, B20 becomes B21, B22, B23, B24, etc… and then all I need to do is enter a number into column B and it will link me to the sheet with the corresponding number.

To Hyperlink to the INFO sheets it is the same formula, just change SHEET to INFO. You also don’t have to use a funny symbol you could use words if you like.

It sounds more difficult than it really is.