Forum Discussion

eviera49's avatar
eviera49
Copper Contributor
Nov 15, 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's avatar
    PeterBartholomew1
    Nov 15, 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.

eviera49's avatar
eviera49
Copper Contributor
Feb 09, 2021

eviera49 

Help!

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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 09, 2021

eviera49 

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.