Forum Discussion
How to Reference the Last Cell in a Column in a Table?
Sheet One with TableSheet Two Referencing Prior Table
I have a table where I add data at the end of every day. I would like a formula to reference the last value in a column. Is there an easy way to do that? In the photos above, I would like B5 on Sheet2 to reference the value in E14 on Sheet1, which is the last value in KeyThree column.
I am not well versed in commands for tables.
Aside from specific table commands, by knowing the count, I could use index or offset to reference the appropriate cell. But I am curious if there is a more elegant solution where I don't have to keep track of the count.
I have uploaded this simple spreadsheet.
I look forward to your comments. Thank you.
- Thank you for your reply. I looked at your and JMB17's reply to create me own.
Initially, I mentioned about the count. I did not want to have to manually keep track of the count, but I can use the count function to help me.
I am not sure how to show code, but my formula is as follows:
` =INDEX(KeyTable[KeyThree], COUNT(KeyTable[KeyThree]))
It's really just a hybrid of the two prior ideas.
Thank you for helping me with this small problem.
- Thank you for your reply. I looked at your and JMB17's reply to create me own.
Initially, I mentioned about the count. I did not want to have to manually keep track of the count, but I can use the count function to help me.
I am not sure how to show code, but my formula is as follows:
` =INDEX(KeyTable[KeyThree], COUNT(KeyTable[KeyThree]))
It's really just a hybrid of the two prior ideas.
Thank you for helping me with this small problem.Or you can use the offset function for a column of numbers as in:
OFFSET(Bank[[#Headers],[Book Balance]],COUNT(Bank[Book Balance]),0)
Here, Bank is a table & Book Balance is a column name therein so the offset cell is the header to the column Book Balance and so the offset formula points to the last entry in that column.
- Thank you, JMB17, for your reply. It helped me to create my own formula, which I will show in the next reply.
Can you, please, explain your formula? When I look at the XMATCH function, I notice that the first argument is required. Yet you seemed to have skipped the first argument. I'd appreciate knowing how your formula works.
=INDEX(KeyTable[KeyThree],XMATCH(,KeyTable[KeyThree])-1)Below is a link to the XMATCH function.
https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312
KStecyk , both XMATCH and XLOOKUP accept blank as value for the lookup. You may use reference on any blank cell or skip first argument, that is interpreted as blank value. Result will be the same.
Like the INDEX/MATCH this will return a single-cell range reference
= LET( field, KeyTable[KeyThree], XLOOKUP(1, SIGN(field), field,,,-1) )The LOOKUP formula would do fine to return the value.
Thank you, Peter, for your response. While I am confident that your solution works mighty fine, it looks complicated to me. I am okay with the Let function, but the XLOOKUP with all the different arguments seems complicated. Even so, it is good to see different alternatives. Sometimes, though, it is good to do things differently. So I appreciate your help.
A 'novelty' workbook loosely based on your question.
I very much appreciate the time you've taken to create a novelty spreadsheet. I am learning from you.
I found your formulas break if the last value is negative. That is, the formula gives the wrong answer. I tried to create a formula to address this weakness, but my formula doesn't work. I am hoping you can spot my error and fix.
I have included a version 2 of your spreadsheet.
I look forward to your response.
Kevin
I solved my own problem. I had a typo because I copied the wrong formula. If you open the enclosed spreadsheets, you'll see.
I will also enclose version 2a of the spreadsheet.
Thank you!
Kevin
Hi there,
I hope the following formula will help you to find last cell value in a Column in a Table.
=LOOKUP(3, 1/(KeyTable[KeyOne]<>""),KeyTable[KeyOne])
Just in case, that's what Jeff Blakley suggested in very first answer.
It is very possible.
I just was hurry in answering so that haven't mentioned.
Thank you for your information.
- Thank you, Ilgar_Zarbaliyev, for your response.
- I'm using Power Query on Excel for Mac. I save my "iqy" file to a Queries Folder, but then when I go to to Excel and go to Data/Get External Data/Run Web Query, my "iqy" files are all greyed out, except for the sample files from MS which are not greyed out. Does anyone know why my files would be greyed out and how to change that.
Deleted
Rather than hijacking this thread with your different topic, you'll likely get more success with your own separate thread. That way, people can see and focus on your specific question. If they read my question, they might not even bother to read further.
Good luck.
