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: