Reference an infinite range starting from row x

BarneeL
BarneeL ✭✭
edited 12/09/19 in Formulas and Functions

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_123
    L_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.

  • BarneeL
    BarneeL ✭✭

    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_123
    L_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 

  • BarneeL
    BarneeL ✭✭

    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_123
    L_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 Williams
    J. Craig Williams ✭✭✭✭✭✭

    Do the results need to be in the same column?

    Craig

  • BarneeL
    BarneeL ✭✭

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

  • BarneeL
    BarneeL ✭✭

    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_123
    L_123 ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!