Forum Discussion

dp3450's avatar
dp3450
Copper Contributor
May 07, 2021

Getting the value from the last row in a table

I have an excel sheet with multiple tables defined (FIN, AR, MT...etc)

Each table has the same column structure (Income, Expense, Total).

From Each table, I need to get the value from the Total column in the last row and display in another sheet.

My question is how to I get the last row value.

Ex: In my FIN table, the Total is in column F

FIN table has 6 rows. This will be increased every day during data entry.

I need to access the Total in the last row of FIN table and show in the second sheet.

I can get the no of records in my table by using Rows(FIN) function.

So, if I have 6 rows, I need to get F6. If I try to use string concatenate F & rows(FIN), it doesn't work. Error out. Any way that I could get the value from the last row? 

 

Formulas and Functions
    • EequalsMCsquared's avatar
      EequalsMCsquared
      Copper Contributor
      Jul 31, 2024

      HansVogelaar 

      The INDEX answer is nice.

      An alternative that could allow you to do other things too would be to use your idea of making a reference, concatenating F & ROWS(YourTableName) but it needs to be 

       INDIRECT("F" & ROW(FIN))

      You would ALSO need to adjust the answer for however many rows there are above the table. If your table does not start in row 1, the row number for the last row is not equal to the number of rows in the table.  The ROW function would help, especially if you might be want to add or delete rows above the table.

       

      ROW(YourTableName[#Headers]) gives the row number for the first, header, row so your reference would be

       

      INDIRECT("F" & ROW(FIN[#Headers]) + ROWS(FIN) - 1)

      (If the header really is in row 1, you have just added on 1 so you need to take it back off again.) 

      Slightly clumsier than index answer (thanks HansVogelaar ), but a very flexible solution to problems of getting the right reference.  It's a variant of your issue that got me looking at this post and this has solved my own similar problem.

       

      You can combine anything that gives the right reference as text by using the indirect function.

       

      Mark

Resources