Through our tutorials we have shown you how to work with Microsoft Office tools, including the powerful Excel. This time, to enrich your knowledge, we will teach you how to make a dynamic reference in Excel easily.

Spreadsheets in Excel have functions that are key when it comes to getting the most out of them. Among them are both formulas and dynamic reference . We will talk about them below.

Multifunctional tools in Excel

Spreadsheets can contain data or information of a different nature; which users may want to constantly update. For this purpose the formulas and the dynamic reference arise; therefore it is necessary to master the functions of these multifunctional tools.

The formulas

Through the formulas you will be able to carry out mathematical operations of various kinds ; They will be used to: create databases, calculate budgets, keep a relationship of income and expenses, calculate costs, and even create statistical graphics.

The dynamic reference

The dynamic reference represents a function with which it is possible to merge the workbooks in Excel ; which for the business sector could become quite complex.

Therefore, through this function, you can view the specific results of a spreadsheet or other workbook. Thus, the information can be updated in a timely manner.

Steps to create dynamic reference in Excel easily

There are different methods to get information from one sheet to another, or from one book to another instantly; even when dividing an Excel sheet into one or more files based on a column.

However, this time we will show you two alternatives to create a dynamic reference step by step, which are Drop-down list and the INDIRECT function:

Dropdown list

It is the first step that we recommend you take. This list is the one that will facilitate the search and access to the required information in a more systematic way. You create it with the Data Validation command, which you can find in the Data tab of the toolbar.

validacion de datos en excel

Drop-down list example

If, for example, you have a store, where you have different sheets that represent each of the categories: kitchen utensils, or office supplies, among others. If the data you want to synthesize is on another sheet, then:

Go to a new sheet, where you can create a new form and enter the data. Select a range with the information you want. Give it a name per column, for example “Merchandise”, and copy the data.

lista desplegable en excel

Select the drop-down option

Go to the sheet where you want to extract the data, in the Validation criteria option select the List option; go to settings. Now include the range you want to span in your drop down list, which also matches the number of cells selected.

Now go to the Origin option and press the F3 key; In the Paste name window select the name you created and click OK . When you click on the triangle, the name of the categories with the same range previously selected will appear.

INDIRECT function

By means of the INDIRECT function , you will obtain the desired results in the same place, visualizing the data contained in a specific range of cells. You just have to insert the command in the function you want to get.

Example to apply the INDIRECT function

Suppose that you already have the dropdown list in the column called Merchandise, and you must locate the total sold in a category. The information is on another sheet called “kitchen utensils”, so in this case:

When you position yourself on that category you will see the letter of the column and the row number; then the function associated with this cell will appear. For example: (cookware! B: B) in column B.

Set the INDIRECT function

Replace the name of this category by “INDIRECT” (which in other cases could be another name) in the function that will show you for that column: = SUM (INDIRECT (A10 & »! B: B»)).

As you could see, creating or making a dynamic reference in Excel is a detailed process, but it is not complicated when the steps are clear. Also reminding you that through practice you will be able to do better and better. Don’t forget to leave us your comment about this post and share it with your friends.

Helpful?, please rate!
[Total: 0 Average: 0]