SOLVED

Getting the value from the last row in a table

%3CLINGO-SUB%20id%3D%22lingo-sub-2336488%22%20slang%3D%22en-US%22%3EGetting%20the%20value%20from%20the%20last%20row%20in%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2336488%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20sheet%20with%20multiple%20tables%20defined%20(FIN%2C%20AR%2C%20MT...etc)%3C%2FP%3E%3CP%3EEach%20table%20has%20the%20same%20column%20structure%20(Income%2C%20Expense%2C%20Total).%3C%2FP%3E%3CP%3EFrom%20Each%20table%2C%20I%20need%20to%20get%20the%20value%20from%20the%20Total%20column%20in%20the%20last%20row%20and%20display%20in%20another%20sheet.%3C%2FP%3E%3CP%3EMy%20question%20is%20how%20to%20I%20get%20the%20last%20row%20value.%3C%2FP%3E%3CP%3EEx%3A%20In%20my%20FIN%20table%2C%20the%20Total%20is%20in%20column%20F%3C%2FP%3E%3CP%3EFIN%20table%20has%206%20rows.%20This%20will%20be%20increased%20every%20day%20during%20data%20entry.%3C%2FP%3E%3CP%3EI%20need%20to%20access%20the%20Total%20in%20the%20last%20row%20of%20FIN%20table%20and%20show%20in%20the%20second%20sheet.%3C%2FP%3E%3CP%3EI%20can%20get%20the%20no%20of%20records%20in%20my%20table%20by%20using%20Rows(FIN)%20function.%3C%2FP%3E%3CP%3ESo%2C%20if%20I%20have%206%20rows%2C%20I%20need%20to%20get%20F6.%20If%20I%20try%20to%20use%20string%20concatenate%20F%20%26amp%3B%20rows(FIN)%2C%20it%20doesn't%20work.%20Error%20out.%20Any%20way%20that%20I%20could%20get%20the%20value%20from%20the%20last%20row%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2336488%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2336557%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20the%20value%20from%20the%20last%20row%20in%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2336557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1016382%22%20target%3D%22_blank%22%3E%40don075%3C%2FA%3E%20Try%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(FIN%5BTotal%5D%2CROWS(FIN))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2336645%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20the%20value%20from%20the%20last%20row%20in%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2336645%22%20slang%3D%22en-US%22%3EThanks%20that%20works.%3C%2FLINGO-BODY%3E
New Contributor

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? 

 

2 Replies
best response confirmed by don075 (New Contributor)
Solution

@don075 Try this:

 

=INDEX(FIN[Total],ROWS(FIN))
Thanks that works.