Trying to find first and last 15 cells with data within range - how to ignore blanks?

Hi,

Trying to find the first and last 15 cells within a range that contains data - however, there are some blanks between the cells (of varying intervals).

I was able to use the formula post here by NBVC for a range that did NOT have blanks:

=INDEX(All_Data_sheet!A:A,MATCH(REPT("z",255),All_Data_sheet!$A:$A)-10+ROWS($A$1:$A1))

However, this does not work with my new ask.

Thank you,
Rich

Update:

Found a formula to find the FIRST 15, ignoring the blanks by using the array:

=IF(ROWS(L$1:L6)>COUNTA(B:B),"",INDEX(B:B,SMALL(IF(B$2:B$256<>"",ROW(B$2:B$256)),ROWS(L$1:L6))))

How would I adjust this formula to find the LAST 15?

Originally Posted by daddylonglegs

Try replacing SMALL with LARGE.....

You can also use IFERROR in Excel 2010 to shorten a little, i.e. for last value try this formula

=IFERROR(INDEX(B:B,LARGE(IF(B$2:B$256<>"",ROW(B$2:B$256)),ROWS(L$1:L1))),"")

confirm with CTRL+SHIFT+ENTER and copy down for 2nd last, 3rd last etc......

Perfect, thank you.

Adding on to my original post -

The array takes the bottom 15 cells with data, but returns beginning with the last row - Is there a code where I can get the bottom 15 of, for example, a 30 row range but start with row 16?

Originally Posted by daddylonglegs

The ROWS part at the end is giving you 1 then 2 then 3 etc so if you make it 16-ROWS(L$1:L1) that will go from 15 to 14 to 13 etc. so that will look like this:

=IFERROR(INDEX(B:B,LARGE(IF(B$2:B$256<>"",ROW(B$2:B$256)),16-ROWS(L$1:L1))),"")

If there are fewer than 15 entries then the first rows will be blank.....

So sick, thank you.