SOLVED

# Getting the value from the last row in a table

Copper Contributor

# 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?

5 Replies
best response confirmed by don075 (Copper Contributor)
Solution

# Re: Getting the value from the last row in a table

@don075 Try this:

``=INDEX(FIN[Total],ROWS(FIN))``

# Re: Getting the value from the last row in a table

Thanks that works.

Thanks!

Paul

# Re: Getting the value from the last row in a table

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.

`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

# Re: Getting the value from the last row in a table

A lot of functions have been added since the original post. You could use:

``=TAKE(FIN[Total],-1)``
1 best response

Accepted Solutions
best response confirmed by don075 (Copper Contributor)
Solution

# Re: Getting the value from the last row in a table

@don075 Try this:

``=INDEX(FIN[Total],ROWS(FIN))``