How to Provide Table Reference in Another Sheet in Excel

Say we have Sale data in Table format for March’22 for three different cities, New York, Boston, and Los Angeles. These three sets of Sales data are identical in orientation, so we show only one set of sales data as a dataset:

Dataset-Excel Reference Table in Another Sheet

In this article, we’ll use Structured References, Insert Link, and HYPERLINK functions to reference a Table in another Excel sheet.

Before introducing our methods, it’s convenient if we assign specific names to our Tables, so we can simply type their names when referencing them.

STEPS:

  • Select the entire table or place the cursor in any cell of the table.

Excel displays the Table Design tab.

  • Click on Table Design.
  • Assign a Table name (i.e., NewYorkSale) under the Table Name dialog box in the Properties section.
  • Press ENTER.

Excel then assigns the name to this Table.

  • Repeat these Steps for the other 2 Tables (BostonSale and LosAngelesSale).

Naming Table-Excel Reference Table in Another Sheet

  • Check the naming using Formulas > Name Manager (in the Defined Names section).

You will see all the assigned Table names in the Name Manager window.

Name Manager-Excel Reference Table in Another Sheet

Now we can easily reference them in formulas.

Read More: Types of Tables in Excel: A Complete Overview


Method 1 – Using Structured Reference

Structured Reference means we can reference an entire column by just providing the header name in the formula along with the assigned Table name.

Steps:

  • Start typing a formula after inserting an Equal Sign (=) in the Formula bar.
  • Enter a Table name to reference it, as shown in the image below.
  • Excel brings up the Table reference; double-click on it.

Structured reference-Excel Reference Table in Another Sheet

  • After referencing the Table, type a third bracket ([).

Excel shows column names to select from.

  • Double-click on Total Sale and close the brackets.

Formula insertion

  • Assign New York Sale Table, then one of its columns (Total Sale).

Both arguments are indicated in colored rectangles.

Indications

  • Press ENTER to apply the formula in cell C5.

Formula result

  • Follow the steps above to reference the other Tables in the respective cells.

After referencing, Excel shows the sum of the Total Sale columns of the respective Tables as follows:

Final outcomes

You can reference any Table just by assigning its name in the formula, along with the column header you want to deal with.

Read More: Does TABLE Function Exist in Excel?


Method 2 – Using Insert Link

Excel’s Insert Link is an effective way to link or reference cells or ranges from other sheets. As we are referencing a Table, we need to use the Table range to reference it by link. Prior to the reference, we have to check the range that our Table occupies, similar to the picture below.

Insert link-Excel Reference Table in Another Sheet

Steps:

  • Place the cursor in the cell (i.e. C5) where you want to insert the reference of the Table.
  • Go to Insert > Link > Insert Link.

Insert link

The Insert Hyperlink dialog box opens.

  • Click on Place in this Document as the Link to option.
  • Select the Sheet (‘New York) under Or select a place in this document.
  • Type cell reference B4:F12 under Type the cell reference.
  • Edit or keep what Excel takes as Text to display (i.e. ‘New York’!).
  • Click on OK.

Insert link window

Clicking OK inserts the link of the Table in cell C5.

Link insertion

  • You can cross-check the referencing just by clicking on the link as shown below.

Cross-check

After clicking on the link Excel takes you to the destined worksheet and highlights the entire Table.

Destination

  • Use the Steps above to insert links for the other Tables (Boston Sale and Los Angeles Sale).

Final outcome

This method is suitable when the table and the sheet name have the same name.


Method 3 – Using the HYPERLINK Function

The HYPERLINK function converts a destination and a given text into a hyperlink. We can then instantly move to a worksheet on demand just by clicking on the links.

The syntax of the HYPERLINK function is:

HYPERLINK (link_location, [friendly_name])

In the formula,

link_location; the path to the sheet to which you want to jump.

[friendly_name]; the display text in the cell where we insert the hyperlink [Optional].

Steps:

Paste the following formula in cell C5 or any blank cell:

=HYPERLINK("#'"&B5&"'!NewYorkSale",B5)

In the formula,

#'”&B5&”‘!NewYorkSale = link_location.

B5 = [friendly_name].

HYPERLINK function-Excel Reference Table in Another Sheet

  • Press ENTER.
  • Insert the same formula in the other cells after replacing the respective Table Names.

Result

Read More: Excel Table vs. Range: What Is the Difference?


Download Excel Workbook


Related Articles


<< Go Back to Excel Table | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo