[traveler911]

I am looking to display the the number where the event has occurred in sequential order.
Column C has N for true, or O for false (Wingding font)
What I want to do is display the running total for true events that occurred sequentially without interruption for the entire year. The first example shows the sequence in the last column. (counting from 6 to 13)

Kind of if C4=n, then 1, ""; then I guess nest a SUM to keep a running total?

Part two. Hopefully the sequence will extend to the next year. So in the second example you can see the current year, and below is the sequence from the previous year. Short example is 2013 ends with 8, so 2014 will start with 8, and add on from there.

Thanks
Kent

Mo 10/14 n 3.103 0:33:15 10:43 389.0 5.60 22:00 1.0 1.0 H 3.1 6
Tu 10/15 n 8.014 1:24:23 10:32 1007.0 5.70 13:10 1.0 1.0 H 5 7
We 10/16 n 8.045 1:23:40 10:24 1004.0 5.77 19:45 1.0 1.0 H 6.2 8
Th 10/17 n 2.013 0:21:03 10:27 247.0 5.74 05:10 1.0 1.0 W 2 9
Fr 10/18 n 6.706 1:10:05 10:27 824.0 5.74 13:50 1.5 1.5 H 6.2 10
Sa 10/19 n 2.110 0:22:13 10:32 263.0 5.70 05:20 1.0 1.0 W 2 11
Su 10/20 n 3.104 0:32:41 10:32 386.0 5.70 14:50 1.0 1.0 H 3 12
Mo 10/21 n 2.012 0:20:51 10:22 247.0 5.79 05:20 1.0 1.0 W 2 13


2013 2014
2012 0 2013 8
N 1 n 9
N 2 n 10
N 3 n 11
O n 12
O o
N 1 o
N 2 n 1
N 3 n 2
N 4 n 3
N 5 n 4
N 6 n 5
N 7 n 6
N 8 n 7

[Kevin-Radstock]

Hi

How about a sample spread sheet with dummy data and your expected result instead of typing all that.

[traveler911]

Hi

How about a sample spread sheet with dummy data and your expected result instead of typing all that.

I didn't see the attach file buttom when I wrote this, other wise believe me I would have.

Column O is the field in question, and it references C. The sequence starts in rows 240's. This is for 2013, and hopefully the streak will continue into 2014. 2014 has it's own sheet. So then I am assuming once we figure this out, and I can just add a sum to reference a cell that I enter the total from 2013. I know I can have it linked to the field, but I am afraid that if I end up deleting this sheet in a few years it will cause a cascade of information loss if they are all referencing the previous sheet.

Thanks
Kent

[JBeaucaire]

THe formula you're looking for might be this, for example in O4, then copied down:

=IF(C4<>"n", "", N(O3)+1)


You can use that same formula on your new sheet, but for the first cell on that new sheet you reference the LAST cell from the 2013 sheet:

=IF(C4<>"n", "", N('2013-Workout'!O331)+1)

To avoid the possible loss of data from year to year, after you get the value from the first cell correct and you SEE the result, you can change it from a formula to a flat value. The rest of the formulas in column O refer to the same sheet, so they are safe.

[traveler911]

I came to post another question, and I found that my last reply on this thread disappeared. I wanted to thank you for your assistance. It worked out perfectly.

I don't understand why the reply didn't stay.

Thanks
Kent

[JBeaucaire]

That's OK, no need to fret, even forums are subject to occasional quirks and flukes. Thanks for coming back to let me know.