Posts by leema

Thanks Gerry and Roy! Both excellent advice - and I can tailor this perfectly.

Really appreciate your help!

Hi there

Please see attachment, I'm trying to automatically populate columns with the correct order of months, ensure they hit their 30/31 day max day count and for Feb (depending upon a leap year or not), reach 28 or 29 days. I'm a beginner, so struggling with how to add all of these formulae together! Basically:

If month in column A is Jan, Mar, May, Jul, Aug, Oct or Dec then don't let the count/number in column B be >31 (eg: months with 31 days).

If months in column A is Apr, Jun, Sep or Nov, then don't let the count/number in column B be >30 (eg: months with 30 days).

If the number in D2 is 1, 5 or 9 (based on the Chinese calendar) AND if month in column A is Feb, don't the number in column B be >29 (eg: a leap year), otherwise don't let it be > 28

Any ideas?!!!

Many thankstest_140620.xlsx

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!

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!

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!

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!

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

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!