webforms with look-ups and formulas

Si Spence

Hello smartsheet fans,

I've created a smartsheet for the sale team to populate with Customer Contract information which will then get shared with our supply chain colleagues.  Some cells contain look-ups and some cells contain formulas...and so far all is working as it should.

I thought it would be good to have the team complete the smartsheet via webform but then realised that that look-ups don't seem to work in web-forms and also formulas don't calculate on the SS.  

Is this usual, or am I missing something.

Thanks

Shaine Greenwood

Hi Si,

Formulas won't perform calculations in a form, only in the sheet after a submission is made.

I'm guessing that your formulas either aren't auto-populating in new rows as people submit entries via form, and/or you're expecting your LOOKUP formula's reference range to expand.

For a formula to autofill, the same formula needs to exist 2 rows above, 2 rows below or 1 row above and one row below—all on the same hierarchical level. Autofill also knows when it could potentially create errors, so it won't autofill a formula when the logic is such that it would reference a row that doesn't exist yet. This typically happens if your formulas reference rows below them.

As for your LOOKUP formula, if it's referencing a range of specific cells, and a new row gets added below that range or above it, the range won't expand to reference those new rows. You you may need to adapt your formula to reference the entire column range instead. Details on this are in our Creating and Editing Formulas article.

Mike Wilday

Yes, you need to make sure that the bottommost row of your Smartsheet contains whatever formula you are desiring new rows to take to ensure that they get auto-filled. I find that frequently a user will add a blank row, or a manual row to the bottom of my sheet and when the new web form populates it doesn't fill the data accurately. Manually entered data breaks the auto fill feature from populating on web forms that are entered. 

Si Spence

Thanks Shaine,  I'm pretty sure I follow most of this...I'll do some more tinkering and let you know how I get on.  Si.

Si Spence

Good Tip Mike,  Thank you.

Mike Wilday

You're welcome.

ker9

With sheet formulas, I found it better to have the web form rows come in at the top rather than the bottom, it seems to work better at retaining the formulas that way.

Mike Wilday

Great suggestion ker9. That would totally avoid the issue of extra rows and formulas being overlooked. 

OAP

Hi, need to be quite crafeul with how many formulas you put in a sheet.

 

We have a formula based sheet that is driven by a webform also - for the purpose of trackign time etc. 

It can make the sheet completely unusable (just grinds to a halt) even with just moderate formula usage nowhere near the capability that is advertised or woudl generally be exected from a spreadsheet system

Have rebuilt it in Google Sheets and it works a  dream - no idea why SMartsheet struggles so badly with formulas driven sheets?