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:

=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

becomes

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