Linking Multiple Worksheets

This article details how to link multiple worksheets when using expanding rows and columns in Excel with Report Designer. 

Single Cell Named Range

To link multiple sheets using a single cell named range, follow these steps:

1. Create a basic report with Account on the row as an expanding group, and Period on the column, all on the first tab, Sheet1. Add a measure to the cells, such as an Amount (Value1).
2. Right-click the sheet tab and select Copy under Report Designer. Right-click the same tab and choose Paste as New under Report Designer.
   
3. Remove the Period and Amount references from Sheet2 so that Sheet2 is only bringing in Amount.
4. Return back to Sheet1 and create named ranges for Account and Period. Ensure that the named range includes an extra row or column when using expanding groups.
   • In this example, cell F6 contains Period. Select F6, click the Named box in the top left, and type “SinglCell_Period”, then press Enter.
     
   • Under Formulas in the Excel Ribbon, click Name Manager to view a list of all the named ranges that have been created.
     
5. Go to Sheet2 and enter this formula into cell F6: =INDIRECT(“Sheet1!SingleCell_Period”) where Sheet1 is the tab where the named range SingleCell_Period was created in step 3.
   
6. Execute the report and the period should flow through. As an example, if cell F6 on Sheet 1 is period 201001, then the formula will output 201001 on the second tab.

Expanding Group Named Range

In this example, we want to bring in the data for a particular account for a particular period. Building on the example above, we will create a named range for our data and use Excel’s MATCH formula to compare two values.

To link multiple sheets using an expanding group named range, follow these steps:

1. Using the previously created named ranges, apply a This Year All function to the Periods grouping on Sheet 1.
2. Next, create a named range for the data to be brought into the form. Keep in mind, that this named range should go one cell over and one cell down (2×2) in order to accommodate expanding periods and accounts. This named range can be called Data.
3. Create the formula to bring in the values for the accounts.
   • On Sheet 2, we need to spread the Period INDIRECT over 12 columns so that all periods are returned. To avoid having the sheet return #Value, add an IFERROR clause to the INDIRECT formula to return blank when the formula errors out.
   • Go to Sheet 2 and select the cell where the measures would go (cell D8 in the example below). Type this formula:
     =INDEX(INDIRECT(“Expanding_Sheet1!Expanding_Data”),MATCH($B7,INDIRECT(“Expanding_Sheet1!Expanding_Account”),0),MATCH(F$6,INDIRECT(“Expanding_Sheet1!Expanding_Period “),0))
   • This formula incorporates the use of Excel’s “INDEX MATCH MATCH” with the INDIRECT formula to compare a row and column value and return a value where the two intersect.
     
   • As done in step 1, spread the INDIRECT formula over 12 columns to cover all the data.
   • The final step is to add an IFERROR clause for when the formula errors out. In this case, we will have the equation return a dash, “-“, when there is an error.
     =IFERROR(INDEX(INDIRECT(“Expanding_Sheet1!Expanding_Data”), MATCH($B7, INDIRECT(“Expanding_Sheet1!Expanding_Account”),0), MATCH(F$6, INDIRECT(“Expanding_Sheet1!Expanding_Period”),0)),”-“) 

Now, the formula is looking up the account and matching it to each period column from Sheet 1. Execute the report to view the results.

See the attachment for more information.