"Smartsheet Formula Examples" template cell references issues

Jamie Bedford

The "Smartsheet Formula Examples" template, while very helpful, appears to have a variety of issues with cell references that have been moved over time. Somewhere around row 26 things start to get weird.

Is this only on my copy of this sheet? I did delete it from my workspace and reload it to see if it would be fixed, but the issues remain.

• The INT() example does not actually use the formula
• The COUNT() example uses a range that goes from row 50 to row 24 (backwards? I guess this is OK)?
• The LEN() example seems like it should be getting the length of the phone number, but actually gets the length of the number "24" two rows below the phone number.
• etc.

More issues all the way down the template, including some that make it difficult to understand what the functions are meant to do. There are also some cell references that appear to be broken, and yet somehow actually still work?

Is this going to be updated any time soon? If so, please fix the references! Thanks! 😀

Genevieve P.

Hi @Jamie Bedford

Thanks again for bringing this to our attention. I'll let the template team know about this & we'll do some testing on our side to resolve this.

Cheers,

Genevieve

Genevieve P.

Hi Jamie,

This is very strange as I downloaded the template and have different results:

• The COUNT() example is backwards still, but it doesn't matter how you specify a range (backwards or forwards)
• The LEN() example seems is looking at the phone number cell
• The MAX() is looking at a range where 31 is the Max.

I would suggest deleting the sheet, then deleting it from your Deleted folder, and try downloading it again? If this doesn't work, then please post here for any of the functions/formulas that you would like more information or examples of and I'd be happy to help.

Cheers,

Genevieve

Jamie Bedford

Thanks for the quick reply! I just deleted the sheet/folder from my deleted items as well, then reloaded it, and it still appears to be broken in the same way as before...

Is it possible that this is somehow cached on my side, or in my company's tenancy? Or maybe you have a different version?

The version on mine indicates it is "version 5.0 (July 2019)"

Jamie Bedford

Perhaps I should open a support ticket via my company's admins?

Genevieve P.

Hi Jamie,

I have the same version, 5.0, July 2019. You are more than welcome to submit a Support Ticket; I will also raise this to my team to see if we can find out what the cause may be.

In the meantime, are there specific functions you would like to see examples of? The Community is a great resource in that regard! And I'd be more than happy to help you with any formulas you're looking to build.

Jamie Bedford

Thanks! I actually have forgotten what I was looking up when I noticed this weirdness -- probably something related to VLOOKUP / Index/Match/Collect functions as I had been trying to build a report from multiple sheets with a common identifier column. I found a variety of other resources for this, though, so at the moment I'm unblocked :)

Genevieve P.

Haha! Alright, sounds good - let me know if you do need any help, and I'll get back to you next week with what I find in regards to the template.

Thanks for flagging the issue! I'd love to know if others are experiencing the same thing.

Genevieve P.

Hi @Jamie Bedford

In looking into this further, we realized that you are on a .gov account, which has a different template.

Thank you so much for bringing this to our attention! Our team was able to make changes based on your feedback and replace the .gov template with an updated version.

Please try deleting the template and re-download it. Let me know if you have any other issues!

Cheers,

Genevieve

Jamie Bedford

Ah, I meant to mention that -- sorry about leaving that important detail out! I didn't realize that the templates would differ, though. Good to know!

I was able to get the updated version of the formula examples template (v6.0 May 2020), but I still see a lot of the same issues with the references in the formulas, which seem to start around line 31 ("LEN" function). (Though there are some weirdnesses higher up in the sheet, too, like the range in the formula on row 26.)

Is it possible that the changes the team made were not saved correctly in the new template?

Genevieve P.

Hi Jamie,

It looks like the LEN function there is working correctly, but just looking at the wrong cell (row 33 instead of row 31). I'll pass this update along to our team as well. Thanks!

In the meantime, I would suggest using our Help Center as the guide for your formulas (see here), and of course you can always post specific questions in the Community for help understanding how to add the functions together.

Jamie Bedford

To clarify a bit further: There are many additional functions that seem to be referencing unintended rows below row 31. It would take me a while to call them all out individually, which I don't have the bandwidth to do currently :\

I'm guessing that the .gov version of this template is made from a copy of the commercial version, but with some rows removed? Probably rows that explain formulas that are not available in the .gov version (or are just different)?

The reason I guess that is because I just noticed with one of my own sheets that when rearranging / removing rows, sometimes formulas below those rows don't update their references as intended/expected... this results in formulas with weird references, kind of like what seems to have happened in the .gov version of this template.

To elaborate on my example: I had a main bunch of rows and then some "summary" rows below it [which I realize should be in a report]. The formulas in the summary rows do a bunch of IFS and SUM and COUNTIF type aggregations. These were working fine until I re-sorted the main bunch of rows above them. After re-sorting, the formulas in the summary rows got all messed up [though they did remain at the bottom of the sheet, which is nice].

Jamie Bedford

Just checked in to this again, and it seems that the gov't version of the formula example reference is still broken in many cells.

Row #s that appear to be broken: 28, 31, 32, 33, 34, 35, 38, 39, 44, 49-58, 66-78, 90, 96-124, 144, 164, 165, 168?, 172, 191, 196, 201, 206, 211, 216, 221, 226, 244, 255

@Genevieve P - FYI - I just deleted (and permanently removed from trash) my copy of the Formula Samples Sheet, and re-added it. The version I have is labeled "version 6.0 (May 2020)" and has errors in the above listed rows' formulas. Generally these are issues where the formulas are pointing to the incorrect values/sample data sets.

Genevieve P.

Hi @Jamie Bedford

Thanks again for bringing this to our attention. I'll let the template team know about this & we'll do some testing on our side to resolve this.

Cheers,

Genevieve

Jamie Bedford

Thanks!