Book Image

101 Excel 2013 Tips, Tricks and Timesavers

By : John Walkenbach
Book Image

101 Excel 2013 Tips, Tricks and Timesavers

By: John Walkenbach

Overview of this book

Excel is a popular program. Millions of people throughout the world use it on a regular basis. But it’s a safe bet that the vast majority of users have yet to discover some of the amazing things this product can do. 101 Excel 2013 Tips, Tricks, & Timesavers?is packed with information that you need to know in order to confidently and seamlessly master the challenges that come with using Excel! Excel 2013 is excellent, but there's lots to learn to truly excel at Excel! In this latest addition to his popular Mr. Spreadsheet's Bookshelf series, John Walkenbach, aka "Mr. Spreadsheet," shares new and exciting ways to accomplish and master all of your spreadsheet tasks. From taming the Ribbon bar to testing and tables, creating custom functions, and overcoming "impossible" charts, mixing nesting limits, and more,?101 Excel 2013 Tips, Tricks, & Timesavers?will save you time and help you avoid common spreadsheet stumbling blocks.
Table of Contents (10 chapters)
Free Chapter
1
Cover
2
Table of Contents
3
Title Page
4
Introduction

Tip 50: Using the INDIRECT Function

To make a formula more flexible, you can use the Excel INDIRECT function to create a range reference. This rarely used function accepts a text argument that resembles a range reference and then converts the argument to an actual range reference. When you understand how this function works, you can use it to create more powerful interactive spreadsheets.

Specifying rows indirectly

Figure 50-1 shows an example that uses the INDIRECT function. The formula in cell E5 is

=SUM(INDIRECT(“B”&E2&”:B”&E3))

9781118642184-fg05001.tif

Figure 50-1: Using the INDIRECT function to sum user-specified rows.

The argument for the INDIRECT function uses the concatenation operator to build a range reference by using the values in cells E2 and E3. So, if E2 contains 2 and E3 contains 4, the range reference evaluates to this string:

“B2:B4”

The INDIRECT function converts that string to an actual range...