Best Of

Re: Data Shuttle Upload - Merge & Update based on key column value causing errors

Both configs rely on a 1:1 mapping, so if there are multiple rows that match the uniqueID, its only going to update one of them.

I have a similar solution in place at my company to handle this type of workflow.

I go from excel to a helpersheet with a DataShuttle Config. The config completely replaces the data in the helpersheet upon upload.

I then go from the helpersheet to my central Smartsheet repository using Datamesh with a copy/add relationship. The datamesh target only has a single row for each unique identifier.

With this method, your contract renewals would be treated as updates to the original parent contract that is already on the datamesh target, rather than added as new rows.

jessica.smith

1 ·

Re: February Question of the Month - Join the conversation and receive a badge

Dashboard Guru

TamieKing

16 ·

Re: February Question of the Month - Join the conversation and receive a badge

If I could make a badge for Smartsheet, I'd make a series of badges for each of the community ranks. (Observer, Seeker, Contributor, Counselor, Leader, and Ambassador) As people achieve higher and higher ranks, their badge portfolio would be updated to mark each new milestone.

It would also be neat to poll the community of people who've achieved certain ranks and potentially vote on designs for these badges. Or, we could just leave it up to the Smartsheet team that already designs badges - they always put out some bangers! (e.g. these new QoM badge designs!)

Will Duplex

27 ·

Re: Calculating time

Smartsheet is not set up for calculation time durations the way it is dates. However, I think I figured out a way to do this even listing times as you have here.

First, you need to create two "helper" text columns that will help isolate the hour markers and convert them from text to recognizable values (can be hidden once set up). Additionally, it will calculate how far in the hour your minutes by adding it as a decimal.

In the "Start Time Hour Helper" column, I have the following column formula:

=IF(LEN([Start Time]@row) = 7, VALUE(LEFT([Start Time]@row, 1)) + (VALUE(MID([Start Time]@row, 3, 2)) / 60), IF(LEN([Start Time]@row) = 8, VALUE(LEFT([Start Time]@row, 2)) + (VALUE(MID([Start Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))

In the "End Time Hour Helper" column, I have the following column formula:

=IF(LEN([End Time]@row) = 7, VALUE(LEFT([End Time]@row, 1)) + (VALUE(MID([End Time]@row, 3, 2)) / 60), IF(LEN([End Time]@row) = 8, VALUE(LEFT([End Time]@row, 2)) + (VALUE(MID([End Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))

Now, in your "Training Duration (min)" column, you need to be able to show the difference in hours multiplied by 60 minutes, while also accounting for the chance that hours may cross over from AM to PM since this example does not use military time. For this column, I wrote the following formula:

=IF(OR(AND(CONTAINS("AM", [Start Time]@row), CONTAINS("AM", [End Time]@row)), AND(CONTAINS("PM", [Start Time]@row), CONTAINS("PM", [End Time]@row))), ([End Time Hour Helper]@row - [Start Time Hour Helper]@row) * 60, IF(VALUE(LEFT([End Time]@row, 2)) = 12, ([End Time Hour Helper]@row - [Start Time Hour Helper]@row) * 60, (([End Time Hour Helper]@row + 12) - [Start Time Hour Helper]@row) * 60))

Assumptions:

-Sessions are in the same day (none that cross the overnight threshold)

-You will write start and end times one of two ways (with either 7 or 8 characters, including spaces). To ensure accuracy, I would add all the hours in the day as drop down values in your Start and End Time columns formatted the same way, assuming sessions start at predictable intervals (i.e. every half hour, 15 minutes, hour, etc.)

Here's a screenshot of what the finished product of these formulas looks like on my screen:

In any case, this was a fun one to figure out. Hope this helps!:)

brianschmidt

2 ·

Re: Automate sending a form

It sure would be nice to have a form designer when you create an Update Request Workflow - creating logic is nice if I am requesting an update and the user is going to complete the ticket/row and I want to have another field come up to be filled. What is the point of being able to create multiple forms if they are ONLY used to create a new row.

Even better would be a Dynamic form that pulled data - i.e. use an Auto-Number field as a dropdown at the top of a form to fill any current data and allow the user to update or complete them. The problems with having used Access for years is I get used to the functionality of Forms pulling data - obviously I am not going back to access but what can you do SmartSheet?

Michael Berman

2 ·

Re: IF(AND(OR? Formula

I think I see what you are trying to do, give this a try:

=IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Standard"), 95 * [Hours Needed]@row * 0.3, IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Non-Standard"), 115 * [Hours Needed]@row * 0.3, ""))

Carson Penticuff

1 ·

Re: CountIFs

Give this a try:

=COUNTIFS([Travel State]:[Travel State], OR(@cell = "CO", @cell = "TX", @cell = "WA", @cell = "OK", @cell = "NM", @cell = "NE", @cell = "MO", @cell = "MN", @cell = "ID", @cell = "IA"))

Carson Penticuff

1 ·

Re: Forms and entries

@Cristian,

Great question! This gets asked a lot. Unfortunately a user cannot return to a form. Not for editing their previous entry, nor can they save half way through and return to complete it.

There are Product Ideas regarding this that you can upvote to help gain attention by Smartsheet's development team.

All the best,
-Ray