Excel: Return Values Across Multiple Sheets for Organizations Within Specific States

Excel: Return Values Across Multiple Sheets - Organizations within Specific States

In this article, we will explore how to use Excel to return values across multiple sheets, focusing on organizations within specific states. This is especially useful when you have a workbook with multiple sheets for different states and want to analyze data for a particular state or compare data across states.

Assumptions and Setup

Let's assume you have an Excel workbook with one master sheet named "Organizations_US" that contains data for all organizations across the United States. You decide to create additional sheets for specific states, such as "Organizations\_CA" for California, "Organizations\_NY" for New York, and so on. Each sheet has the same structure: an "OrganizationID" column and other columns with relevant information.

Using INDIRECT and SUMIFS Functions

To return values across multiple sheets, we can use a combination of the INDIRECT and SUMIFS functions. In this example, we'll try to find the total donations for a specific organization (OrganizationID = 1001) in the California sheet.

1. First, let's create a cell reference to the specific sheet you want to pull data from. In our case, it is the "Organizations\_CA" sheet.

This formula creates a reference to range A2:B100 in the "Organizations\_CA" sheet.

2. Now, let's use SUMIFS to find the total donations for OrganizationID 1001:

This formula finds cells with OrganizationID = 1001 in column A of the "Organizations\_CA" sheet and sums the corresponding donation amounts in column C.

Extending to Multiple Sheets

To extend this to multiple sheets, you can create another column that lists the state abbreviations (CA, NY, etc.) and their corresponding sheet names. This way, you can use a combination of INDIRECT, MATCH, and SUMIFS functions to make your formulas more dynamic. In the example below, column E contains the state abbreviations, and column F contains their sheet names:

With this formula, you can simply change the cell reference in column F (F2) to switch between different state sheets.

• Excel allows you to return values across multiple sheets with the INDIRECT, SUMIFS, and other functions.
• The INDIRECT function creates a reference to a range in a specific sheet.
• The SUMIFS function finds specific values in columns and returns corresponding data, such as sums of amounts.
• You can make your formulas more dynamic by combining INDIRECT, MATCH, and SUMIFS functions.

References

• Microsoft Excel INDIRECT function
• Microsoft Excel SUMIFS function
• Using INDIRECT function across multiple sheets