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

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?