Get last entry in a Range with no Blank Cells.

[Stealth2017]

I have a Range AB29:AO30 of which a Number will be entered starting at AB29 to AO29 and then AB30 to AO30. Not all cells will have a number every time but there will be no blank cells. eg. a number will be in cells AB29 to AO29 and AB30 to AE30. I need to get the last Entry (in this case AE30) and display it in Cell AS29. The last entry could be in any of the cells in the Range. What Formula do I need to do this.

[Glenn Kennedy]

your description is ambiguous. What is the role of row 29?

try this:

=LOOKUP(10^100,AB30:AO30)

[JohnTopley]

Try

=OFFSET($AB$29,INT((COUNT($AB$29:$AO$30)-1)/14),MOD(COUNT($AB$29:$AO$30)-1,14))

[Stealth2017]

Hi Glenn, This Range AB29:AO30 is in a Darts Score Sheet Singles Match and the players score is entered after their throw starting at AB29, AC29 etc. If the match is not completed by cell AO29 the next score is entered in AB30, AC30 etc. Depending on the players ability the match could end in Cell AD29 or any Cell after that. So I need a formula that finds the cell with the last entry.

[JohnTopley]

See post #3.

[Stealth2017]

Thanks John, that works. I have incorporated your formula into the score sheet with a couple of extra statements. The reasons for this is when there is no score in AB29 we don't want anything in AS29 and if a player forfeits we put 501 into cell AB29 and don't want anything in AS29. I also need another statement added that when the total of the cells reaches 501 (AQ29 shows this) your formula runs and puts the last score into AS29 but should be blank if total is below 501. With the following formula cell AS29 shows ### whenever a score of less than 501 is in cell AQ29. How do I fix this minor glitch.

=IF(AB29="","",IF(AB29=501,"",IF(AQ29=501,OFFSET($AB$29,INT((COUNT($AB$29:$AO$30)-1)/14),MOD(COUNT($AB$29:$AO$30)-1,14)))))

Bill Slaney.

[JohnTopley]

Try


=IF(AB29="","",IF(AB29=501,"",IF(AQ29=501,OFFSET($AB$29,INT((COUNT($AB$29:$AO$30)-1)/14),MOD(COUNT($AB$29:$AO$30)-1,14)),"")))

So only runs when AQ29=501

[Stealth2017]

Thanks John that works well. Thank you for your assistance and very quick response. Bill

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.