Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Shrikant S. wins an e-book of Learn Excel 97-2007 From MrExcel from CFO and MrExcel for his question: “Every month I download an Excel file from our financial reporting system that contains the P&Ls of various cost centers (over 100 tabs, one for each of the cost centers). I have to generate an Excel report summarizing the Revenue, Operating Income, and EBITDA for each of the cost centers. Currently, I manually select data and do a copy paste to a new sheet. Is there an easier way to summarize data from various tabs into a new sheet? Each of the tabs is uniform in terms of the columns and rows that it contains. [However,] our financial reporting engine cannot do this for us, and that is why we export to Excel and do this manually.”
The fact that the financial reporting engine is putting income in the same row and column every time makes this problem much easier to solve. I am also going to make a simplifying assumption that the worksheet tabs are named after the cost center with perhaps some modifier and that there already is a handy range of cost centers available in Excel.
In Figure 1, I’ve added a Summary worksheet to the data downloaded from the financial reporting package. In column A, I’ve pasted the list of cost centers. Notice that the worksheet for cost center 1000 is named C1000.
Build a formula to link from the Summary worksheet to the Income cell on the first cost-center worksheet. Follow these steps:
- Select cell B4 in Figure 1.
- Type an equals sign.
- Using the mouse, click on the worksheet tab for C1000.
- Click on the cell for Revenue. In my case, this is cell H4.
- Press Ctrl+Enter to complete the formula and stay in cell H4.
Look at the formula in the formula bar. It will look something like this:
You will have to figure out the rule for creating the cell reference in a formula. In Figure 1, the cell reference would be comprised of:
• an apostrophe,
• a capital letter C,
• the cost center from $A4,
• an apostrophe,
• an exclamation point, and
• the cell address of H4.
The following text formula will build that reference:
In Figure 2, you can see a temporary formula that would return the correct cell reference for each cost center.
The goal in Figure 2 is to make sure you can build a text formula that concatenates a combination of cell values and text in quotes to replicate the formula in Figure 1 (without the leading equals sign). If you can build a formula that builds text that looks like a cell reference, then you are home free.
Using the INDIRECT Function
Excel has a function called INDIRECT that will take text that looks like a cell address and return the value from the cell address. The INDIRECT function is allowed to point to the current worksheet or to any worksheet in the current workbook. (Unfortunately, it cannot refer to an external closed workbook — however, it would be a very useful addition.)
Figure 3 shows the INDIRECT function wrapped around the formula from Figure 2. After the equals sign in Figure 2, type:
Then close the formula with a closing parenthesis, once again using both quote marks and apostrophes, like this:
Copy the formula down.
Filling the Remaining Columns
You may have noticed the single dollar sign before A4 in the above address. This locks the cell reference to always return a value from column A. This was not necessary in the above formula, but it makes it easier after you copy this formula to the right. Copy the formulas from B4 and paste to C4:D4. Edit the formula in C4. Change H4 to the appropriate cell to get Net Income. Next, edit the formula in D4. Change the H4 to the appropriate cell to get EBITDA. It is interesting to note that since H4 is part of a text expression, it does not automatically change to I4 and J4 when you copy it to the right (see Figure 4, below).
Bill Jelen is a CFO contributing editor, and the author of 32 books about Excel, including Excel Gurus Gone Wild. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right. Bill’s next live Webcast, “Making Workbooks Look Great for Your Next Annual Meeting,” is on March 10. For more information, click here.