Forum Discussion
How to Reference the Last Cell in a Column in a Table?
- 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.
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
What you are doing is perfectly correct. From the original example, I was guessing how you wanted to treat text, zeros, negative numbers, Booleans, Errors.
I would also note that the formula at the foot of the table could be simplified by taking advantage of relative structured referencing, but that does not apply to the lookup from sheet 2 or the conditional formatting.
= LET( header, KeyTable[#Headers], field, KeyTable[KeyTwo], XLOOKUP(TRUE, ISNUMBER(field), field,"empty",,-1))Thank you very much for all your help and patience. I understood your last formula. Although it looks a bit intimidating at first, it makes intuitive sense and is elegant.
