I recently saw a post on LinkedIn about making a drop-down list for a hyperlink interface. It was using a range name and the ADDRESS function. I thought I could streamline it with just the CELL function.

In the image below I have several sheets named after colours, with a list of their names in column A.

The drop-down settings in cell C2 are shown in the image below.

The magic happens in cell D2 where the HYPERLINK function creates a hyperlink to the sheet named in cell C2.

The formula is in cell D2 is.

=HYPERLINK(“#”&CELL(“address”,INDIRECT(“‘”&C2&”‘!A1″)),”link”)

The CELL function using the “address” argument creates a text string as shown in cell D4 in the image below.

This text structure is very close to what is required as the first argument in the HYPERLINK function. All it needs the # symbol added to the front.

The INDIRECT function creates a cell reference in the other sheet. INDIRECT creates a cell reference from text. This is used by the CELL function to return the text string shown in the above image.

The # on the front of this text string is treated like a bookmark reference by the HYPERLINK function and it will navigate to the cell address when you click on the link text in the cell.