Last n cells with text

[merchant60b]

I am looking for a formula that will display the last, second last and third last cells etc. in a row that have a text value in them.

My spreadsheet contains the results of a sports team in date order across a row. The results are listed as "W" for win, "D" for draw and "L" for loss. There are various blank cells where a team did not play and for future games.

A1 is team name, B1 is the first result all the way up to AR1 which will be the final result of the season.

To create a form guide, I would like a formula that will pull out the last 6 cells with a text value and set out in 6 cells, and will update where a new result is added.

Thanks

 

[Marcelo Branco]

To get the last 6 cells maybe...

Formula in AT1 copied across until AY1
=INDEX($B1:$AR1,AGGREGATE(14,6,(COLUMN($B1:$AR1)-COLUMN($B1)+1)/($B1:$AR1<>""),COLUMNS(AT1:$AY1)))

Hope this helps

M.