I have a Power Automate web data extraction that pulls price information off of a website for all of the page results weekly. I have the information extracted in to an excel sheet that shows the 10 prices per page along with the page number. I would to create a table that will show me the first and last price shown on each page however, i need it to find the last price on the last page and that can change from week to week. For example, last week the last price was the 5th entry on page 9, this week it's the 1st entry on page 10.
I am using an xlookup formula to pull the first price for each page: =xlookup(1,'Turkey'!$B;$B,'Turkey'!$C:$C,"",0,1) and then another xlookup to pull the last price on the page: =xlookup(1,'Turkey'!$B;$B,'Turkey'!$C:$C,"",0,-1) but this does not identify the last price on the last page. In this case it returns a last value of 0 because the last entry on the last page is blank. The last price entry is actually the 9th entry on page 10. I've also tried using the formula =lookup(2,1/('Turkey'!C:C<>""),'Turkey'!C:C) but i have to adjust that formula each week based on the number of entries.
Is there any formula i could use that would automate this last entry shown for me without having to change it each week?
I am using an xlookup formula to pull the first price for each page: =xlookup(1,'Turkey'!$B;$B,'Turkey'!$C:$C,"",0,1) and then another xlookup to pull the last price on the page: =xlookup(1,'Turkey'!$B;$B,'Turkey'!$C:$C,"",0,-1) but this does not identify the last price on the last page. In this case it returns a last value of 0 because the last entry on the last page is blank. The last price entry is actually the 9th entry on page 10. I've also tried using the formula =lookup(2,1/('Turkey'!C:C<>""),'Turkey'!C:C) but i have to adjust that formula each week based on the number of entries.
Is there any formula i could use that would automate this last entry shown for me without having to change it each week?