View original (French)

Incremental numbering

AP976 Posted messages 11 Status Member -  
AP976 Posted messages 11 Status Member -
Hello,

I want to move from one cell to another while descending in the same column by “sliding” the cells. This involves adding +2 to the source row between each cell.

In other words, I’d like a formula that goes from:
=CONCATENER(NB.SI(JAN!$G$9:$BR$9;"BVS0600W01");"/";SI(JAN!$O9="";"";1)+SI(JAN!$AC9="";"";1)+SI(JAN!$AQ9="";"";1)+SI(JAN!$BF9="";"";1))
to the case B2, to:
=CONCATENER(NB.SI(JAN!$G$11:$BR$11;"BVS0600W01");"/";SI(JAN!$O11="";"";1)+SI(JAN!$AC11="";"";1)+SI(JAN!$AQ11="";"";1)+SI(JAN!$BF11="";"";1))
for case B3.

So incrementing by 2 along the B column of Sheet1, taking as source row 9 of sheet JAN for case B2 on Sheet1, then row 11 of JAN for case B3 on Sheet1.

I remain at your disposal.

Best regards

Have a good day

2 answers

  1.
    via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Hello

    It is necessary to recompose an address with INDIRECT, which gives:
    =CONCATENER(NB.SI(INDIRECT("JAN!G"&2*ROW()+5&":BR"&2*ROW()+5);"BVS0600W01");"/";IF(INDIRECT("JAN!$O"&2*ROW()+5)="";0;1)+IF(INDIRECT("JAN!$AC"&2*ROW()+5)="";0;1)+IF(INDIRECT("JAN!$AQ"&2*ROW()+5)="";0;1)+IF(INDIRECT("JAN!$BF"&2*ROW()+5)="";0;1))

    Attention in the real IFs you must not have "" but 0, otherwise the sum will give #VALUE! if an IF returns "".

    Best regards
    Via

    --
    "A picture is worth a thousand words." A. Einstein
    1
    1. AP976 Posted messages 11 Status Member
       
      Hello via55, Thank you Yes I understand for 0 in place of "". Regarding the INDIRECT function, I don’t understand how it works; could you please explain it to me? Specifically, why +5? Thank you in advance Best regards
      0
    2. AP976 Posted messages 11 Status Member
       
      Hello via55, Arriving at 100 iterations, it shows 0/0. I don’t see where to modify to change that. Best regards
      0
  2. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    The INDIRECT function reconstructs a valid address by concatenation
    For example INDIRECT("JAN!G" & A1 & ":BR" & A1) reconstitutes the address JAN!G3:BR3 if A1 is 3
    In your case it must be a bit more complex because on line 2 of your table you need 9 in the formula, on line 3 you need 11, on line 4 you need 13
    a bit of math (solving a system of equations) shows that the number in the formula is equal to line*2 plus 5:
    2*2+5=9
    3*2+5=11 etc
    hence & 2*ROW()+5 in the formula

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    1
    1. AP976 Posted messages 11 Status Member
       
      Okay, thanks, that clears things up for me.
      0