View original (French)

HOW TO INCREMENT WITH AN OFFSET

willymontana Posted messages 10 Status Member -  
willymontana Posted messages 10 Status Member -
Good evening everyone
I want cell B to be incremented as follows:
a shift of 3 for example
B1 = A1
B2 = A4
B3 = A7
B4 = A10
I have a formula that does this increment:
=INDIRECT("$A$"&((ROW($1:1)-2)*3+3)+1) but I am looking for another formula because this one requires all the files to be open. I tried the INDIRECT.EXT formula at first it worked but then it bugged and signaled errors, so if there is another simpler way to increment with a shift please show it to me. (especially since the A and B cells are not in the same workbook) I want to use &((ROW($1:1)-2)*3+3)+1 without writing INDIRECT because I don't want to work with this formula. Thank you in advance to everyone who tries to help me, I have developed a large application based on this formula so please help me.

3 answers

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

    I don't see any reference to other workbooks in your formula

    But it's hardly possible to do without INDIRECT for a reference resulting from a concatenation

    however, you can try using a macro in VBA
    https://www.commentcamarche.net/faq/32961-vba-excel-lire-dans-un-classeur-ferme

    Best regards
    0
  2. willymontana Posted messages 10 Status Member
     
    Thank you for your response, I tried your example but it doesn't work. To be precise, here is my formula:
    =INDIRECT("[EXEDENT.xlsb]Sheet1!$A$" & ((ROWS($29:29)-2)*111+111)+29)
    and the link is: C:\Documents and Settings\walide\Desktop\STATISTIQUE 2013\DECISIONS STATISTIQUE 1ER SEMESTRE 2013\DECISION WALID
    0
    1. via55 Posted messages 14395 Registration date   Status Member Last intervention   2 759
       
      I'm stuck here!
      The INDIRECT function should work but requires the workbooks to be open

      Does anyone have an idea?
      0
  3. willymontana Posted messages 10 Status Member
     
    I found a formula to offset rows without using INDIRECT; it gives:
    =OFFSET([EXEDENT.xlsb]Sheet1!$C$29;111*ROW()-999;0) instead of using
    =INDIRECT("[EXEDENT.xlsb]Sheet1!$A$"&((ROWS($29:29)-2)*111+111)+29)
    but unfortunately, even this formula requires that the workbooks be open.
    0