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

Tax reform complicates year-end tax planning

Get your clients ready for tax season with these year-end tax planning strategies, which address how to make the most of recent tax law changes, such as the new deduction for qualified business income and the cap on the deductibility of state and local taxes.

VIDEO

What RPA is and how it works

Robotic process automation is like an Excel macro that can work on multiple applications, says Danielle Supkis Cheek, CPA. RPA can complete routine, repetitive tasks such as data entry, freeing up employee time from lower-level chores.