Forum Discussion
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.
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 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.
The sheet name forms part of the string to be converted to the reference
= INDIRECT("Sheet2!D"&J2)
Have you looked at SergeiBaklan '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.
