Forum Discussion

Copper Contributor

Nov 16, 2020

Solved

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 ad...

Formulas and Functions

PeterBartholomew1
Nov 16, 2020
eviera49
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.

PeterBartholomew1

Silver Contributor

Nov 16, 2020

eviera49

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.

mikemitch

Copper Contributor

Aug 30, 2021

PeterBartholomew1 I have a formula in one sheet where I am taking data from another sheet therefore using this type of formula "=Sheet2!D26" - I've tried using your suggestions above but it comes up invalid. I have tried the following:

"=Sheet2!(INDIRECT("D"&J2)"

"=Sheet2!(=(INDIRECT("D"&J2))"

"=Sheet2!(INDIRECT("D",J2)"

"=Sheet2!(=(INDIRECT("D",J2))"

And the same with the INDEX function. Is this not possible to do when taking data from another sheet? The J2 is from the current sheet. Any help welcome. thanks.

PeterBartholomew1
Silver Contributor
Aug 30, 2021
mikemitch
The sheet name forms part of the string to be converted to the reference
= INDIRECT("Sheet2!D"&J2)