Use last value in a column, increment it by 1 and start a new column with this value. If value>x, then start the count in this column, from 1

Hi there

Excel newbie - managed to use other's codes and modify to use last value in a column (column C), increment it by 1 and start a new column with this value.

CONCATENATE("$C$",(14+(COUNTA($C$14:$C$37)−1))))+1

However, I need to reset the count from 1 if this number is above 10, but I can't figure out how to combine (I think?) an IF statement to do this!

Eg: If value from the last cell in the previous column>10 then start the count in the next column, from 1

any help much appreciated!

Thanks Logit, Fluff and Bosco - I've tried these, but it doesn't seem to work, the results are;

Logit - I replaced your 'x' with the first cell beginning the new column but it didn't work, I also tried the last cell of the previous column (even though that's not quite what I want, as the last cell cannot be predicted, as it depends on how many entries there are), but still no joy.

Fluff- It says 'the range C14-C37 cannot be used as a single value'...so maybe the code was pretty close, but needs tweaking?

Bosco - it says 'TEXT' isn't a valid function - what should I use in it's place?

I'm not a coder, but I get the feeling the answer is not far from what you've given me! Any other tweaks you can suggest?!

Thanks

Probably should clarify:

Essentially, 2 columns (and will scale it up later).

First column has a range of incremental numbers that use a 'previous number +1' formula to increment them, unless they get to 10, then they begin from 1 again: IF(Cell=10,1,Cell+1) where Cell is the active cell - this works fine.

The code for the 1st cell in the second column is: INDIRECT(CONCATENATE("$A$",(14+(COUNTA($A$14:$A$37)−1)))) - eg: it checks the last range of column A (cells 14-37), grabs the value of the last row in that column and sticks it in the first cell of the second column after adding 1 to the value. This also works fine.

But I'd like to ensure (just as the code used in the cells of the first column reset the count after 10), that when the second column is auto-populated with the last value in the first column (+1) that if this new value >10, then reset to 1 again.

Thanks a lot!

Hi Fluff - I created it a while ago in Excel on my laptop, but it's not working at the moment, so I converted it to numbers on a Mac (v6.1). I've attached the excel export of a test sheet with the formulae. It's to build a Chinese calendar, in order to work out more complex timings based on a lunar-solar integration (but I've not got far because of the code!).

test_1.xlsx

Thanks for looking into it, much appreciated!

I changed the code at the top of the second column to this:

IF(LOOKUP(9.99E+307,$B$5:$B$37)>9, 1,"")

This now grabs the value from the last cell in column B, if it reaches 10, then the second column counts from 1 - just what I want. The problem is, I need the formula to say (if this is not the case and the last value is not 10, then just add 1 to the value).....either way, I get stuck with one problem or the other!

thanks Fluff and Bosco...works fine in Excel on another machine....(oops, should have realised Numbers on Mac wasn't that intelligent!!). Thanks again for your help!