Editor’s Note: 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.

Reader Walter J. wins a copy of the new third edition of Learn Excel 2007-2010 from MrExcel for his question, “I am looking for a way to reference a row number for data located in one spreadsheet that is based on a reference number in a cell located in a summary spreadsheet. Here’s the situation: In my summary spreadsheet I have several cells that reference data from an another spreadsheet in the same workbook. The data table is updated weekly by date, and I need to reference a new row number each week. Further, I would like to insert a row number in a cell in the summary spreadsheet and use that cell to reference the row number in the formula that references the data in the data spreadsheet.”

The solution could involve any one of the triumverate of arcane Excel functions; INDEX, OFFSET, or INDIRECT. All three of these functions have the ability to refer to a cell by looking at a value in another cell. In this case, since Walter has a row number available in his Summary worksheet, I will use the INDEX function.

How Index Works
The INDEX function will return a value from a range using row and column numbers. The function has several syntax variations, but here are the most common three ways to use INDEX.

If you want to return numbers from a one-column range, you can specify the range and the row location within the range. In Figure 1, below, the range is B4:B11. The formula is asking for the third row within this range. The result is the 4 in cell B6. This is because B6 is the third row in this range.

Fig. 1

If you are returning numbers from a one-row range, you specify the range and the column within that range. Below, in Figure 2,  the formula is asking for the eighth value in B16:I16. The result is 128 from cell I16.

Fig. 2

When you have a rectangular range, you specify the address of the range, then the row number, then the column number within the range. Below, the range is B22:D35. The formula is asking for the third row and second column. The result is the 10004 from cell C24 because this is the third row and second column in the range.

Fig. 3

Using Index to Solve the Problem

Walter has a data worksheet. It might look something like this:

Fig. 4

He also has a Summary worksheet where he has added actual row numbers. In row 4 of the Summary worksheet, he wants to return a value from the fifth row of the Data worksheet.

In this case, I would specify the entire column as the range. Start the formula with              =INDEX(Data!B:B,

Then, specify the row number from cell A4 as the second argument in the INDEX function. Since you might want to copy this formula across several columns, use a single dollar sign before the A to make sure that the formula is always pulling the row number from column A.  =INDEX(Data!B:B,$A4).

Figure 5 shows the formula copied down to other rows and columns. It returns the proper value from the Data worksheet.

Fig. 5

Bill Jelen is the author of 33 books about Microsoft Excel. For a chance to win a copy of one of his books, post a question at the Community Center to the right. If Bill selects your question as the topic of a future column, we’ll send you a book as a thank-you. Bill’s next MrExcel webcast,
on consolidating data for financial reporting, is Thursday, July 14, at 2:00 Eastern Time.