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.

hyperlink excel defined table1

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

Formula in cell B3:

=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")

Formula in cell B4:

=HYPERLINK("[Quickly select a data set or an excel defined table.xlsx]Sheet2!Table1[#All]", "Table1")

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_valuelookup_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