How to Provide Table Reference in Another Sheet in Excel

Sometimes, we have data in Excel Tables and each time we look for any value or items, we have to go to a certain worksheet. Excel reference Table in another sheet is a handy way to deal with existing data in another worksheet. Excel features such as Structured References, Insert Link, and HYPERLINK function can reference Tables from another sheet.

Let’s say we have Sale data for March’22 of three different cities; New York, Boston, and Los Angeles in Table format. These three Sale data are identical in orientation, so we show only one sale data as a dataset.

Dataset-Excel Reference Table in Another Sheet

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


Download Excel Workbook


3 Ways to Provide Table Reference in Another Sheet in Excel

Before performing any methods, it’s convenient if we assign specific names to our Tables. As a result, we can simply type their names while referencing them.

🔄 Select the entire Table or place the cursor in any cell. Excel displays the Table Design tab instantly.

Click on the 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 the Steps for the other 2 Tables (i.e., BostonSale, LosAngelesSale).

Naming Table-Excel Reference Table in Another Sheet

🔄 You can check the naming using Formulas > Name Manager (in the Defined Names section). And you see all the assigned Table names in the Name Manager window.

Name Manager-Excel Reference Table in Another Sheet

Since we have assigned the Tables specific names to easily refer to them, now we move to reference them in formulas. Follow the below methods to reference Tables in another sheet.


Method 1: Refer a Table in Another Sheet Using Structured Reference

We named Tables specifically depending on their data. Excel offers Structured Reference with Table. Structured Reference means we can reference an entire column by just providing the header name in the formula along with the assigned Table name.

Step 1: Start typing a formula after inserting an Equal Sign (=) in the Formula bar. Then, type 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

Step 2: After referring to the Table, type a third bracket (i.e.,[ ). Excel shows column names to select from. Double click on Total Sale and close the brackets as pictured below.

Formula insertion

🔼 We assign New York Sale Table at first then one of its columns (i.e., Total Sale) later. We indicate both arguments in colored rectangles.

Indications

Step 3: Use the ENTER key to apply the formula in the C5 cell.

Formula result

🔼 Follow Steps 1, 2, and 3 to reference other Tables in the respective cells. After referencing Excel shows the sum of the Total Sale columns of respective Tables as depicted in the below image.

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: Applications of Absolute Structured References in Excel Table Formulas


Method 2: Using Insert Link to Provide Table Reference

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

Insert link-Excel Reference Table in Another Sheet

Step 1: 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

Step 2: The Insert Hyperlink dialog box opens up. In the dialog box,

Click on Place in this Document as Link to option.

Select the Sheet (i.e., ‘New York’) under Or select a place in this document.

Type cell reference B4:F12 where resides the Table 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 the C5 cell.

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 Steps 1 and 2 to insert links for other Tables (i.e., Boston Sale and Los Angeles Sale).

Final outcome

Keep in mind that, this method would be suitable when you have the table and the sheet name in the same way (same name).

Read More: How to Use Excel Table Reference (10 Examples)


Similar Readings


Method 3: Using HYPERLINK Function to Refer Table

As we want to reference Tables from another sheet, we can use the HYPERLINK function. The HYPERLINK function converts a destination and a given text into a hyperlink. For this purpose, we can instantly move to a worksheet as per our demand only by clicking on the links existing in a Worksheet.

The syntax of the HYPERLINK function is

HYPERLINK (link_location, [friendly_name])

In the formula,

link_location; path to the sheet you want to jump.

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

Step 1: Paste the following formula in any blank cell (i.e., C5).

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

In the formula,

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

B5 = [friendly_name]

HYPERLINK function-Excel Reference Table in Another Sheet

Step 2: Hit ENTER then insert the same formula in other cells after replacing it with the respective Table Names as depicted in the following image.

Result

Read More: Use Formula in an Excel Table Effectively (With 4 Examples)


Conclusion

In this article, we use Structured Reference, Insert Link, and HYPERLINK function to Excel reference Table in another sheet. Structured Reference referencing is the most convenient way to reference a Table. Also, other methods also work just fine. Hope these above-described methods excel in their purpose in your case. Comment, if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo