Excel reader challenge

BY J. CARLTON COLLINS

In the August 2013 JofA, I challenged readers to solve an Excel problem in which a preformatted report fetched an ever-changing last row of data. We received well over 200 solutions from a group of Excel-savvy CPAs. Though each solution was different, all of them worked properly and employed a wide range of creative and imaginative approaches to solving the problem. In summary, the readers employed eight basic approaches, with VLOOKUP being the most popular approach (used by 31.3% of the respondents), followed by the INDEX approach (used by 29.3% of the respondents). The approaches I identified (and their frequency of use) are summarized in the chart below.

 

The solutions submitted showcase the ingenuity and industriousness of this column’s readership, and while everyone who submitted an entry deserves recognition, I identified the solutions submitted by Michael Inouye, Laura Douglas, Larry Moseley, Robert Loudon, Trisha Allen, Brian Carle, and Benjamin Brenner as being especially noteworthy because they were efficiently written, contained clever formulas, and taught me things about Excel that I did not previously know. To view a more detailed summary of the submitted solutions, and to download more than 70 of these solutions (including the solutions from the seven individuals mentioned above), visit tinyurl.com/n56bphh.

J. Carlton Collins ( carlton@asaresearch.com ) is a technology consultant, CPE instructor, and a JofA contributing editor.

SPONSORED REPORT

Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.