TechRepublic readers have a full range of expertise. Often, solutions are simple — no VBA needed — but the reader doesn't know the full range of what's available. This month, I help two readers by referring them to existing features for quick solutions. I also suggest that a reader restructure data to simplify sheet referencing. For your convenience, you can download the example .xlsx and .xls file.
Amanda's PivotTable displays company and year values. Each company can have more than one year. She wants the table to return the most recent year record only. Fortunately, Amanda's solution is just a step away: specify the MAX() aggregate in the Values section. We can illustrate this with any two-column data set (see Figure A) in place. Follow these steps:
- Click any cell inside the data set.
- Click the Insert tab.
- Click PivotTable in the Tables group. In Excel 2003, PivotTable is on the Data menu.
- I inserted the table in the existing sheet to simplify the example (Figure A). Click OK when you're ready to continue.
- In the PivotTable Table List, drag Company to the Row Labels section. At this point, you might be tempted (don't do this) to drag Year to the Column Labels section, but doing so won't give you the desired results (Figure B). If you did it anyway — I know some of you did — drag Year out of the Column Labels section before you continue. In Excel 2003, the frame is a dialog box. Select a field, choose an area, and click Add To.
- To force a single-column configuration, drag Year to the Values section (Figure C). Excel defaults to the SUM() aggregate. In Excel 2003, choose Data Area.
- To reset the SUM() aggregate, click the Sum Of Year drop-down (in the Values section). In Excel 2003, right-click one of the subtotaled year values (Total column) in the actual PivotTable.
- Choose Value Field Settings from the submenu.
- In the resulting dialog, choose Max in the Summarize Value Field By (Figure D) on the Summarize Values By tab.
- Click OK to see the results shown in Figure E.
Find & Replace
Brenda has a paragraph of email addresses separated by semi-colons. She wants to convert the paragraph into one column of addresses. Using Find & Replace, this problem has an easy-to-implement solution:
- Select the paragraph and press [Ctrl]+[h] or click Replace in the Editing group (on the Home tab).
- In the resulting dialog, enter ; (a semi-colon) in the Find What control.
- If necessary, click the More button, and then click Special (at the bottom).
- From the resulting list, choose Paragraph Mark (Figure F) or Manual Line Break.
- Click Replace All, then Yes and OK to confirm the task. Click Close to return to the document. Figure G shows the resulting list.
Whether you use the Paragraph Mark or the Manual Line Break depends on how you want to use the resulting list. The difference between the two is the paragraph structure. Using the Paragraph Mark, each list item becomes its own paragraph. If you replace with Manual Line Break characters, Word will see the entire list as one paragraph.
Simplify by restructuring
Romain's workbook has 12 sheets, one each for January through December. He asked for an easy way to carry over a summarized value from one month to the next. The problem is, as you might suspect, that the summarized value he wants to track changes grow from month to month, because each month has a different number of records. There are numerous ways to handle this problem; some are more complex than others:
- Use a macro to find the last cell for each month and transfer the literal value — hard work because finding the last cell in a sheet is fraught with pitfalls.
- If you're using a newer version that supports the table object, grabbing a summarized value at the bottom of the table is easy using table nomenclature.
- Assign a range name to the summary cell and refer to it. That can become a bit unwieldy as you must work the sheet reference into the expression or give each summary cell in each sheet a unique range name.
When something seems like it's taking more work than it should, consider rethinking the design or structure to make things more stable. In this case, the road to simplifying the solution is stabilizing the position of the summary cell. If you can accept a non-traditional design, the solution is simple — put the summary cell at the top of the sheet in the same cell for every month.
To illustrate the problem, let's look at the simple data set in Figure H. If the number of reported projects isn't consistent from month to month, there's no easy way to determine where the SUM() function is from one sheet to another. You can spend a few minutes updating the next month's sheet — in this example, doing so would be simple enough, but it isn't necessary. Instead, move the SUM() function to the top of the dataset.
A summarizing expression or function works just as well above the dataset as below.
As you can see in Figure I, I inserted two rows to the top of January, so the structure is the same for all 12 sheets. That way, you can reference the same cell in the previous month, in each sheet. Referencing each sheet for the previous month, January!B4, February!B4, March!B4, and so on isn't a single-formula solution, but it's easier to manage than a changing cell.
The summarizing cell is the same in each sheet.
With a bit of planning, you can replace the formulas in B2 (the ones that reference different sheets by month) with one INDIRECT() function. Then, the only thing you'll have to update from sheet to sheet is the name of the previous month in A2 (Figure I). Figure J shows February and March. As you can see, A2 contains the name of the previous month, and B2 contains the new INDIRECT() function:
This formula evaluates as follows in February's sheet:
In the March sheet, it'll refer to B4 in the February sheet. In the April sheet, it'll refer to B4 in the March sheet, and so on.
Replace the sheet referencing formulas with a single INDIRECT() function.
If the INDIRECT() function returns an error, check the spelling of the month in A2. If you accidentally misspell a month's name, the function can't reference the sheet.
You can insert the INDIRECT() function in one sheet and copy the sheet (similar to a template), updating the previous month in A2 for each sheet. If the sheets already exist, group them and enter the function once for all of the sheets in the group. (Generally, the first sheet in the sequence won't need an INDIRECT() function.)
About the table object
To learn more about Excel's table object, read the following articles:
- Use Excel's table nomenclature to quickly create dynamic formulas
- 10 reasons to use Excel's table object
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at email@example.com.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.