Best way to pull data from different columns into one

I have a worksheet that is populated via a form.
Worksheet is currently 300+ columns wide (I didn't build it, I inherited it).
The form is used by facility management technicians. Each time they go to a client site to service facilities, HVAC units, refrigeration units, etc. they complete this form. Yes, they complete in June when they service the units and then AGAIN in August when they service the units, same data, but they need to enter it each time. Seems a bit backwards to me.

So . . . I start somewhere.

I would like to compile a worksheet that lists all the clients with corrsponding location data AND serial #'s so that we can lookup the site prior to a technician getting there. In this way he will have all relevant data about the units he is to service before he arrives.

The technicians only having 'viewing' access. My initial thought was for them to complete a form with the address and then an alert email goes back out to them with the data on the units at that site.

But first . . . getting all the data in one worksheet in a vertical format (so I can use Index/match or similar) as opposed to the 300+ columns currently.

Find more posts tagged with

Forms

Sheets

Reports

functionality

Automations

Accepted answers

All comments

Darren Mullen

@PeggyLang

From what you describe, sounds like you need to transpose the data? Sure, you can do that with exporting the data and transposing it in another spreadsheet like Google Sheets or Excel, then use that to import back in Smartsheet.

As far as needing techs to get all that date on each site. I think it would be a very cumbersome user experience and techs would probably hate it ha.

It would be better to use another interface like Appsheet on top of Smartsheet to make the interaction much more user friendly for the techs…

DynamicView might work ok if you have that available. Otherwise, I think a different interface on top of Smartsheet would be much better.

Have worked on apps like this for field techs using Smartsheet as a backend. I assume you would want to store the data in Smartsheet for other uses like dashboarding, etc?

PeggyLang

@Darren Mullen
It would seem that I have no access to funding for anything other than the tools I currently have on hand. :(

I have previously used reports and data mesh (or was it data shuttle??) to get the data from the mammoth worksheet into a vertical structure. That's an option.

Is this something Bridge could be used for?

Darren Mullen

@peggy lang Does your company use Google Workspace? If so, you have appsheet included.

PeggyLang

@Darren Mullen , not that I'm aware of.

Darren Mullen

@PeggyLang Yes, you could use Bridge to transpose the data.

PeggyLang

@Darren Mullen the only experience in Bridge I have is updating dropdown columns thanks to your youtube video!! They saved the day for sure.
I'll look into how I can utilize Bridge; hopefully cleaner than creating 15+ reports and data meshing them. I think that is how many 'Serial #' columns there are in this worksheet.

Darren Mullen

@PeggyLang Glad that video helped! You're the 2nd person today on the community to say you watched that video!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.