November 30, 2023
How to
Creating linked records in Excel by dynamically referencing other worksheet records allows you to minimize unnecessary data entry. Check how it works!
Arzu ÖzkanHead of MarketingMany of us now use Excel as an online spreadsheet database rather than simple spreadsheets, right? We need to be able to dynamically manage thousands of data, which is increasing day by day. While this can be quite difficult in Excel worksheets full of complex data, a few key features are significant to making our work easier.
For example, instead of repeatedly writing the same data to more than one worksheet and rearranging all worksheets for each change, being able to transfer data dynamically between worksheets via references makes work easier. But more seriously, it prevents incorrect data entry and complexity during data updates. When you update your data once from the main worksheet, it is dynamically updated instantly in all referenced worksheets, thanks to references.
How to refer to another worksheet dynamically in Excel?
There are many functions in Excel, some of which are easy-to-understand functions that we are all used to. Other functions are that are quite complex, require technical knowledge and require a long learning process. One of them is the Excel INDIRECT function.
So, what is the INDIRECT function in Excel and what is it used for?
The INDIRECT function has many functions in Excel. In this article, we will explain to you how you can dynamically reference different excel worksheets with the INDIRECT function.
Excel INDIRECT formula to dynamically refer to another Excel worksheet
The Excel INDIRECT function can be used to dynamically reference related cells in other Excel worksheets.
Let's look at the example.;
Let's say you have two Excel worksheets, and you want to transfer some data from worksheet 1 to worksheet 2. The screenshot below shows how you can do this.
Now let's look at the details of this formula.
The most common way to pulling data from another sheet in Excel is to type an exclamation mark and a cell/range reference after the worksheet name.
For example; WorksheetName!Range
Since the worksheet name usually contains spaces, you'd better enclose it in single quotes to avoid an error.
For example; 'My Worksheet!'$A$1.
Now all you should do is enter the worksheet name in one cell and the cell address in the other cell and concatenate them into a text string. Then feed that string to the INDIRECT function. In a text string, you must enclose every element except the cell address or number in double quotes and link all elements together using the concatenation operator (&).
When you take these into consideration, you will get the following pattern;
INDIRECT("'" & Worksheet's name & "'!" & Cell to pull data from)
Type the name of the worksheet in cell A1 and the cell addresses in column B as shown in the screenshot above. So, you get the following formula.
INDIRECT("'" & $A$1 & "'!" & B1)
Attention: If you are copying the formula into multiple cells, you must lock the reference to the sheet name using absolute cell references such as $A$1. Pay attention to this.
Quick Notes:
If one of the cells containing the name and cell address of the 2nd worksheet is blank, the INDIRECT formula returns an error. To avoid this, you can wrap the INDIRECT function in an IF function:
IF(OR($A$1="",B1=""), "", INDIRECT("'" & $A$1 & "'!" & B1))
For the INDIRECT formula that references another worksheet to work correctly, the referenced worksheet must be open. Otherwise, the formula will return a #REF error. To avoid the error, you can use the IFERROR function, which displays an empty string regardless of the error:
IFERROR(INDIRECT("'" & $A$1 & "'!" &B1), "")
We are aware that it seems quite complex and difficult to memorize. Excel can give you a lot of possibilities once you become an expert at using it. But if that is not your main job and you are just using Excel as a tool, it can be quite time-consuming to learn all these functions and apply them every time. It is difficult to do things with one click in Excel, it has its own language, and you must learn it. If Excel is an online spreadsheet database solution for you, easier Excel alternatives can make your life easier and speed things up.
Is there an easier way to dynamically reference another worksheet?
How to collaborate on Excel has always been a critical question, especially if you work with complex spreadsheets. Because it is very possible that the Excel spreadsheets you collaborate on may be corrupted due to a manual human error. However, Excel alternatives that are easier to manage and tamper-resistant eliminate these risks.
It is possible to convert your data into linked records with just a few clicks with Retable's online spreadsheet solution which is a comprehensive Excel alternative solution. Linked records can be used to transfer data from one field to another or to transfer data between multiple records. Moreover, linked records provide that any changes you make in the main data are reflected in other linked tables, dynamically. Linked records help map complex relationships and quickly find information to streamline your data management and workflows.
Linked records allow you to minimize unnecessary data entry and ensure that the most up-to-date information is available wherever you need it.
Let's see how it works!
- Create 2 worksheets on the same spreadsheet
- Enter your data in the relevant tables and choose your column types
- Let's say you want to import data from the 2nd worksheet to the 1st worksheet, for this go to your 1st worksheet.
- Go to the column you want to set as the reference.
- Set your column type as "Reference Another Table"
- Select the worksheet and column to reference
- That's all, now your reference is ready.
Bonus: You can also bring the other data in the row of the cell in a table you refer to your table with one click via the "Look Up" column.
Let's see how it works!
- Create a "Look-up Values" column
- Select your "Referenced column"
- Select your "Lookup column"
- And save it! That's all!
Now your Look-Up Values column is ready. You can automatically get the date from the referenced table and columns.
Try linking two worksheets in your own online spreadsheets! Easily get records from different worksheets and avoid incorrect data entry, create relational databases by easily creating connections between your data.
Do you need to know more about Excel and it's alternative? Check out our recommend contents to dive deeper!
- Excel dark mode | Benefits of dark mode in spreadsheets
- How to pull data from another sheet in Excel
- Best Excel templates for HR managers - Top 10 templates
