[Solved] How to find the last occupied cell in a column

[Zammy]

SB.2013-14.sample.ods

(100.69 KiB) Downloaded 136 times

First post and pretty much a newbie to calc so let me tell my tale. I've been using this spreadsheet to keep track of our small bowling group for a couple of years now without any problems at all. We like to move folks around so they are not always bowling on the same lanes or with the same people week after week.

I've searched these forums and googled for a solution but have come up empty with everything I've tried so far. What I would like to do seems simple but with my limited knowledge I'm having a hard time. Not sure if it's an array or regular formula. I'd like to have the last occupied cell in column C3-C42 show up in K26 which I will be able to reference on our summary sheet instead of going back through each sheet to see who bowled where. I'm sure having some blank cells in that column is what is giving me fits. We have around 24 bowlers but not everyone makes it to the lanes on a weekly basis as you can see by the individual sheets.

Also, is it possible to reference the next to last pair the individual bowled on even if there are blank weeks, I realize that this would be a different formula. and I would put that info in K27. Any help is appreciated and thanks in advance

[ken johnson]

Try

Code: Select all

=LOOKUP("ZZZ";C3:C42)

in K26 and

Code: Select all

=LOOKUP("ZZZ";C3:INDEX(C1:C42;SUMPRODUCT(MAX((C3:C42<>"")*ROW(C3:C42)))-1))

in K27.
Ken Johnson

[Alex1]

It's easier if you omit the rows with dates when people didn't play. Then the formulas will be
=OFFSET(C1;COUNTA(C3:C44);0)
=OFFSET(C2;COUNTA(C3:C44);0)
It looks like the formulas in H48:J48 and H50 are wrong, and the formulas don't include weeks 37 to 42.
And there are a lot of formulas like =IF(some expression=0;"";some expression)
but you can also suppress the zeros for selected cells by setting the number of leading zeros to zero in the cell format.

[Zammy]

Ken, thank you very much, works perfectly for my needs. Also want to thank Alex1 for his info, I'm going to fix some things on the spreadsheet tomorrow when I have some time. I truly appreciate the help.