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

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

Find more posts tagged with

Sheets

Formulas

Services

Marketing

Product Development

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…