Reference an infinite range starting from row x

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!

Find more posts tagged with

Forms

Formulas

Comments

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

Screen Shot 2018-10-08 at 16.51.37.png

Screen Shot 2018-10-08 at 16.53.17.png

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.