Reference an infinite range starting from row x

BarneeLBarneeL
edited 12/09/19 in Formulas and Functions
10/08/18 Edited 12/09/19

Is there a way that would enable me to reference an infinite range starting from, for example, the 5th row down.

 

Currently, my solution is to use indents and use the CHILDREN([Columnx]x) function.

HOWEVER, unfortunately it seems like this is not a solution compatible with webforms.

 

Is there a way that I can do this, that either doesn't use children, or that enables me to automatically indent new rows added by the webform.

 

Thanks!

Tags:

Comments

  • L_123L_123 ✭✭✭✭✭

    The collect() formula returns an array. If you have an identifying value in the first few rows you can use it to return all lower values.

     

    If you want to parse out the collect formula you can use index() sum() count() or basically use the collect itself as a range. if I knew more about what you want as the end result I could possibly help more.

  • Hi Luke,

     

    Thanks for the info. I could add an identifying value in the first few rows. How would I write that formula? I just had a play around with it myself and couldn't figure it out.

     

    Exactly what I am trying to do:

    I am trying to count the number of times "X" appears in all following, and future, rows after row 5. I can't use the reference [Columnx]:[Columnx] because then it creates a circular reference with a previous formula that is going to reference this 'COUNT' formula I am trying to create.

  • L_123L_123 ✭✭✭✭✭

    Can X be counted multiple times in a single cell? if not, the formula below should work

     

    =COUNT(COLLECT([Column3]:[Column3], [Column3]:[Column3], NOT(@cell = "a"), [Column3]:[Column3], FIND("X", @cell) > 0))

     

    The identifier is that the cell in the same column from rows 1-5 are = "a". you can switch this to whatever you want it to be 

  • Thanks for this. I've just given it a go, and it seems to still result in a circular reference unfortunately. I've put a couple of screencaps below

    Screen Shot 2018-10-08 at 16.51.37.png

    Screen Shot 2018-10-08 at 16.53.17.png

  • L_123L_123 ✭✭✭✭✭

    I'm not sure what to tell you. If you want me to mess around with it you can save a copy and delete not applicable columns, and i'll see what I can come up with. I've posted a link to show the formula I made working, so you can play around with it.

     

    https://app.smartsheet.com/b/publish?EQBCT=f60a34d1586c469a9bce4e2c1b6a379b

     

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    Do the results need to be in the same column?

    Craig

  • Hey Luke, thanks for this. I've edited the smartsheet you sent over to match the problem I am having!

  • Ideally. However, if there is no way to do it other than adding a hidden helper column then I guess I'll end up doing that!

     

  • L_123L_123 ✭✭✭✭✭

    I updated my formula. Let me know if what I put will work for you.

Sign In or Register to comment.