Help with Macro-like Functionality

Hi,

I want to use one cell (call it A1) to accept a numerical entry from the user (a number of hours), and another cell on the same row (call it A2) to automatically update it's value to the sum of A1+A2, and then clear the entry from A1. Each time an entry is made in A1, this would trigger the event again.

It appears there is no way to execute formulas/functions inside a workflow???

This is simple to acheive in excel with a macro and a couple of variables. I don't know if this is possible in SmartSheet without some low-level coding? Maybe some clever formula trickery?

Thanks.

Find more posts tagged with

Accepted answers

KyleW

I figured I would explain the solution I decided upon in case it may be helpful for others. Instead of a robust-yet-complicated system of timestamps/clocking in and out, which is likely to be riddled with human errors in this case (many jobs frequently switched between by multiple engineers, likely resulting in many missed clockings and goofed up labor hour metrics) I decided to take advantage of the "notes" function for the incremental entries of hours, and at the end of a project, when the request/row will be closed, those entries would be manually added up and the result entered into the "Actual Hours" field on the report. This can then be compared with the "Estimated Hours" field, as well as provide metrics for labor hours spent by department, customer, etc.

Using the comments field gives us an historical record of the time entries as well. In case the total does not get entered or is entered incorrectly, we will have the incremental entries on file. Thanks for reading!

All comments

Dale Murphy

@KyleW Can you explain a bit why you want to do this? Depending on the full process you are designing, there are probably ways to get you what you want. (unless all you want is to create the loop you describe ;) )

KyleW

My boss wants a simple way for members of our team to quickly and incrementally input hours spent on a given row in their 'report' (each row is a unique request/project) at any given time throughout the day, or even on the following day, as we are constantly pulled in different directions. The integrity of the resulting data from said method is less than ideal, no doubt, but we do not want to manage a separate sheet or system to collect this data, and it doesn't need to be super accurate. The most important piece to this is that it is effortless and is not dependent on someone's memory to 'clock in' and 'clock out' at accurate start and stop times to collect calculated data from timestamps (this would ultimately be better data if gathered accurately, but given the nature of our work weeks, there would be far too many missed clockings for this to work).

Given my experience with Excel and VBA, I see a simple solution for this that likely would look very different in SmartSheet. In Excel, I would accomplish this by assigning the entered value to a variable, assigning the current total to another variable, changing the total cell's value to be the sum of those variables, changing the entry cell back to null, and reseting both variables. A 3 minute task.

KyleW

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.