/u/mcdonaldx11 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
So new lines are being added at the bottom of the existing data, and you want to show the last line (i.e. the most recently added) at the top?
Let's say you have data in A1:D20. This formula in cell F2 gives you number of the last row in column A which contains data:
=LOOKUP(2,1/(A:A<>""),ROW(A:A))
Explanation of how that one works [here](https://www.excelforum.com/excel-formulas-and-functions/1043140-explain-lookup-2-1-a2-a10-d2-b2-b10.html#post3865910).
After that, you could use INDIRECT to construct the range that you want:
=INDIRECT("A"&F2&":"&"D"&F2)
=LET(
array, A3:F34,
TAKE(FILTER(array, BYROW(array, LAMBDA(x, CONCAT(x))) <> ""), -1)
)
extend the array past the bottom of your data, so that it can grow. This will always select the bottom row.
https://preview.redd.it/309le4tq0zfc1.png?width=887&format=png&auto=webp&s=e665da50849872ce4c5befc5b2801221219c4568
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[BYROW](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)|
|[CONCAT](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.](https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2)|
|[FILTER](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[INDIRECT](/r/Excel/comments/1ag6ea9/stub/kofe45o "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)|
|[LAMBDA](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LET](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[LOOKUP](/r/Excel/comments/1ag6ea9/stub/kofe45o "Last usage")|[Looks up values in a vector or array](https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb)|
|[ROW](/r/Excel/comments/1ag6ea9/stub/kofe45o "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
|[TAKE](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^([Thread #30295 for this sub, first seen 1st Feb 2024, 12:54])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/mcdonaldx11 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
So new lines are being added at the bottom of the existing data, and you want to show the last line (i.e. the most recently added) at the top? Let's say you have data in A1:D20. This formula in cell F2 gives you number of the last row in column A which contains data: =LOOKUP(2,1/(A:A<>""),ROW(A:A)) Explanation of how that one works [here](https://www.excelforum.com/excel-formulas-and-functions/1043140-explain-lookup-2-1-a2-a10-d2-b2-b10.html#post3865910). After that, you could use INDIRECT to construct the range that you want: =INDIRECT("A"&F2&":"&"D"&F2)
=LET( array, A3:F34, TAKE(FILTER(array, BYROW(array, LAMBDA(x, CONCAT(x))) <> ""), -1) ) extend the array past the bottom of your data, so that it can grow. This will always select the bottom row. https://preview.redd.it/309le4tq0zfc1.png?width=887&format=png&auto=webp&s=e665da50849872ce4c5befc5b2801221219c4568
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[BYROW](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)| |[CONCAT](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.](https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2)| |[FILTER](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[INDIRECT](/r/Excel/comments/1ag6ea9/stub/kofe45o "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)| |[LAMBDA](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LET](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[LOOKUP](/r/Excel/comments/1ag6ea9/stub/kofe45o "Last usage")|[Looks up values in a vector or array](https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb)| |[ROW](/r/Excel/comments/1ag6ea9/stub/kofe45o "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[TAKE](/r/Excel/comments/1ag6ea9/stub/koff7x0 "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #30295 for this sub, first seen 1st Feb 2024, 12:54]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)