How to make formulas work on top of a sheet if new entries are coming on the bottom of the sheet via

Noemi Kecskes

Hi All

 

I am really stuck with this as I need to amend my fomulas manually every time I have a new entry. Whenever there is a new entry ( via Web-form) I need to change the formula to include the new row instead of helping me with automation it has created extra work for me.

Did any of you faced a similar problem? What would be the solution?

Thank you

Andrée Starå

Hi Noemi,

Depending on your use case you could have the formula point to the whole column. Then you don't have to worry about it.

Would that work for you?

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant / Get Done

Noemi Kecskes

Hi Andree,

 

I tried what you have suggested, my issue is that if I put 5000 in the formula then my formula doesn't work as the rows do not exist yet/ they are created as the entries are coming in. If I create blank rows up until 5000 then they cannot use the web form as the sheet is full and there is no room for new entries. Any suggestion how this can be solved please?

 

Thank you!

Andrée Starå

Hi again Noemi,

Have you tried columnname:columnname? This takes the whole column in the formula and you don't need to worry about the number.

Example: 

=COUNTIF(Package:Package, [First Name]4)

https://www.smartsheet.com/blog/support-tip-range-formulas

Best,

Andrée

Noemi Kecskes

This works!! Thank you Cheers!

Andrée Starå

Happy to help!

Best,

Andrée