Reference an infinite range starting from row x

BarneeL

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!

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

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

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

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

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

Do the results need to be in the same column?

Craig

BarneeL

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

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

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