How do you automatically reference data or calculations across worksheets in Excel?

In the actual work process, we often need to prepare a variety of templates for other people to fill out. Therefore, the use of data availability to create a choice of drop-down menu is a very common method. In general, we'll make a table that contains all the basics, a list of all the alternatives, such as name, model, department, and so on, and the actual form is another one. So how do you automatically reference data or calculations across worksheets in Excel? The following small series for everyone to introduce, a look at it!

Method/Step

Culvert simple one is directly equal to other worksheet cells, such as: Sheet2 worksheet A1 cells refer to A1 cells in the Sheet1 table, direct =sheet1! The A1 is quoted here.

The second type, given the condition reference. For example, I want to refer to the B3 cell data in the Sheet2 to reference the same data in Sheet1 c column data, as shown in the graph, you can use the VLOOKUP formula. Of course, the first thing to determine is whether the data is unique, because the VLOOKUP function can only refer to uniqueness data

Third, across the worksheet statistics, subtotal totals, you can use the SUMIF conditional formula. As you can see, if you're not looking at an entire column, you should remember to add an absolute reference to the lookup range.

The fourth kind, according to the date range returns the data in the Sheet1 to the Sheet2, as shown here, we have to use the array, according to the conditions to return the value of the condition, and then press the Ctrl+shift+enter key combination.

The fifth kind, use PivotTable report Simple operation, also can cross table classification statistic, as shown: Of course, this kind of statistical data method is relatively quick, but sometimes inconvenient, see individual circumstance use.

Of course, because the data is a link to another table because it is referenced across worksheets, the auto-redo feature in the option must be turned on, or the data will be error-prone.

The above is in Excel how to cross the worksheet to automatically reference data or calculation introduction, hope to be helpful to everyone!