View original (French)

Selection of the last 12 rows in a table

Solved
massimo888 Posted messages 209 Status Member -  
massimo888 Posted messages 209 Status Member -
Hello,
I have a table that is dynamic and continuously adds new data. I would like to retrieve only the values from the last 12 rows.
Is this feasible?
Thank you

Sincerely.

Sam

4 answers

  1.
    ccm81 Posted messages 11033 Status Member 2 434
     
    Hello

    your data in column A, to calculate the average of the last 12
    =AVERAGE(OFFSET(A1,COUNTA($A:$A)-1,0,-12,1))

    Best regards

    corrected formula
    0
    1. massimo888 Posted messages 209 Status Member
       
      Hello,

      I want to retrieve the values but not their averages.
      It's as if we had a copy-paste of the table but only for the lower part.
      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    without your model, just a principle to adapt assuming that column A on sheet1 (or another) of your table has no empty cells interspersed:

    to edit the last 12 values of column A in sheet2 A2:

    =OFFSET(sheet1!$A$1,COUNTA(sheet1!A:A)-12+ROW()-2,)
    to get the value of A and drag down to row 13 to get the total
    the same formula to edit B
    but with:
    =OFFSET(sheet1!$A$1,COUNTA(sheet1!A:A)-12+ROW()-2,1)
    ;2) for C, etc...
    Note
    __COUNTA can refer to any filled column
    __pay attention to the $ signs on $A$1
    __=ROW()-2 must give 0 on the row where the 1st formula is located)
    __the -12 should be adjusted depending on whether A starts in A1 or A2, with or without a title, etc...
    best regards

    To err is human, to persist is diabolical.
    0
    1. massimo888 Posted messages 209 Status Member
       
      Thank you very much, it works on Excel perfectly!!
      0
  3. ccm81 Posted messages 11033 Status Member 2 434
     
    I want to retrieve the values
    Okay, but what for?
    0
  4. ccm81 Posted messages 11033 Status Member 2 434
     
    An example could be
    http://www.cjoint.com/c/EFDnexHDzFg

    Best regards
    0
    1. massimo888 Posted messages 209 Status Member
       
      I collect the values to plot a graph for 12 months only.
      In any case, thank you for your help
      0
    2. massimo888 Posted messages 209 Status Member
       
      Here is the solution in VBA for those interested! 

      Sub test()

       Range("A65536").End(xlUp).Offset(-11, 0).Select
       Range(Selection, Selection.End(xlToRight)).Select
       With Selection
       
       .Copy
       
       Range("h1").Select
       'Selection.End(xlDown).Select
       Selection.Insert
       
       End With
      End Sub
      0