SOLVED

# Using the number in a cell as the row reference in other.

Occasional Contributor

# Using the number in a cell as the row reference in other.

I have a table to which I need to keep adding rows.

At the end of the table, I have several cells taking the values of the last row, and it makes me change the row number in then all every time I add a row.

It would help me a lot if I could  could do this:

Let's say I want the value of F3 in E8.

Instead of writing =F3 in E8, I want to put the the row number 3 in D4 and write a formula in E8 that takes the column F and the row number 3 I wrote in D4.

7 Replies
best response confirmed by eviera49 (Occasional Contributor)
Solution

# Re: Using the number in a cell as the row reference in other.

The function that performs the task you describe is

=INDIRECT("F"&D4)

Another option is to use INDEX

= INDEX(F:F, D4)

I tend to use defined names rather than cell references e.g.

= INDEX( data, instance )

but that is a matter of personal preference.

# Re: Using the number in a cell as the row reference in other.

Most probably it's not necessary to enter row number, it could be calculatable. Especially if use Excel Tables, not ranges.

# Re: Using the number in a cell as the row reference in other.

Thanks! Peter!!

It works perfectly.

# Re: Using the number in a cell as the row reference in other.

Thanks a lot Sergei.

Perhaps this solution is better for people who knows more than I do.

# Re: Using the number in a cell as the row reference in other.

Help!

Is there any way I can use other formula if I add N columns?

# Re: Using the number in a cell as the row reference in other.

Have you looked at @Sergei Baklan 's suggestion of using Excel Tables.  That provides a name for the data range that adjusts as data is added.  The primary use of this functionality to keep track of the number of records in the Table but, if you add fields/columns that works too.

The key point is that the formulas

= ROWS(Table1)

= COLUMNS(Table1)

keep track of the Table size, so that

= INDEX( Table1, 0, COLUMNS(Table1)),

for example, is the right-most field of the table.

# Re: Using the number in a cell as the row reference in other.

Thanks for helping me again.

Please see the whole explanation of the problem in the following post: