## Quickly select a data set or an excel defined table [HYPERLINK]

*Article updated on January 22, 2018*

If you often copy data sets or tables, the following technique might be interesting! The animated gif shows two hyperlinks, the first one selects a data set, the second one selects an excel defined table.

Adding more rows or columns to the data set makes no difference, both the first formula and the excel defined table are dynamic.

This may save you some time if you copy excel tables often.

**Formula in cell B3:**

**Formula in cell B4:**

### Select data in an excel defined table

Table1[#All] - Selects everything, headers and data

Table1 - Selects data

Table1[[#All],[Country]] - Selects data and header Country

Table1[Country] - Selects data in column Country

### Explaining formula in cell B3

**Step 1 - Find last row in the data set**

MATCH(CHAR(255), Sheet2!B1:B1000, 1)

becomes

MATCH("ÿ", Sheet2!B1:B1000, 1)

an returns 102.

Change cell range B1:B1000 if the data set has more than a thousand rows.

Read Create a dynamic named range if you are interested in dynamic cell ranges.

**Step 2 - Find rightmost column in the data set**

MATCH(CHAR(255), Sheet2!A2:CW2,1)

returns 5.

Change cell rangeA2:CW2 if the data set has more than a hundred columns.

**Step 3 - Create cell reference**

ADDRESS(MATCH(CHAR(255), Sheet2!B1:B1000, 1), MATCH(CHAR(255), Sheet2!A2:CW2,1))

becomes

ADDRESS(102, 5)

returns $E$102.

**Step 4 - Add file and sheet name**

"[Quickly select a data set or an excel defined table.xlsx]Sheet2!$B$2:"&ADDRESS(MATCH(CHAR(255), Sheet2!B1:B1000, 1), MATCH(CHAR(255), Sheet2!A2:CW2,1))

becomes

[Quickly select a data set or an excel defined table.xlsx]Sheet2!$B$2:$E$102

**Step 5 - Create the hyperlink**

HYPERLINK("[Quickly select a data set or an excel defined table.xlsx]Sheet2!$B$2:"&ADDRESS(MATCH(CHAR(255), Sheet2!B1:B1000, 1), MATCH(CHAR(255), Sheet2!A2:CW2,1)), "sheet2")

becomes

HYPERLINK([Quickly select a data set or an excel defined table.xlsx]Sheet2!$B$2:$E$102, "sheet2")

### Download excel *.xlsx file

Quickly-select-a-data-set-or-an-excel-defined-tablev2.xlsx

### Functions in this post

**MATCH(***lookup_value*, *lookup_array*, *[match_type]*)

Returns the relative position of an item in an array that matches a specified value in a specific order

**HYPERLINK(**link_location, [friendly_name]**)**

Creates a shortcut that opens a document stored on your harddrive, a network server or on the internet.

**ADDRESS(**row_num, column_num**)**

Creates a cell reference as text, given specified row and column numbers

Quickly jump to last row in a data set using excel hyperlink function

Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take […]Quickly jump to last row in a data set using excel hyperlink function

Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take […]### 2 Responses to “Quickly select a data set or an excel defined table [HYPERLINK]”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

The file (Quickly select a data set or an excel defined table.xlsx) where you try to explain , not working.

Yoram,

The filename was wrong, windows seems to change blanks to - when downloading a file, like this:

Quickly select a data set or an excel defined table.xlsx

to

Quickly-select-a-data-set-or-an-excel-defined-table.xlsx

It works now, try it again.