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

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

How to generate random numbers and text

What's on this page Random numbers with a decimal Random negative numbers with two decimals Random negative numbers with three […]

Use CHAR function to convert a number to the corresponding character. This is determined by your computers character set. Windows ANSI […]

Locate lookup values in a table [HYPERLINK]

Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When […]

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 […]

Create links to all sheets in a workbook

The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]

Use hyperlinks in a pivot table

Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]

Locate lookup values in a table [HYPERLINK]

Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When […]

### 2 Responses to “Quickly select a data set or an excel defined table [HYPERLINK]”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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.