How can I condense information entered from a form to only show the columns that were filled out

I have a request form with 50+ columns/questions. Users will only answer questions that apply to their team. I then have another sheet that a back office team will use to approve and track the work needed to complete the request. I'm looking for the best way to only show only the information in the columns that were filled out. That way, the team isn't trying to asses the information piece by piece as they scroll through the various options.

I was originally going to copy the row of each new request on the assessment sheet and create a summary column that may be easier to show an overview of what was requested, but I can't paste in one swoop since the request is more than 50 columns and I'm not sure how I would only show information that isn't blank, while also showing what column it came from. Is there an easier way around this?

Find more posts tagged with

Accepted answers

All comments

Andrée Starå

Hi @Taylor Riley

One option would be to COLLECT the information together, but only the columns which aren't BLANK.

We could collect the information in one or multiple columns (depending on the character count, if more than 4000, we would need to use multiple) and then show those in a report.

What do you think?

Would that work?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Stefan

Hi @Taylor Riley ,

for my understanding.

You have sheet A with a form creating new rows when users submit the form with their answers. Not every question in the form is mandatory. Somehow the new rows move over to sheet B.

Then you have sheet B (assessment sheet) used by your back office team. For their work a better overview about the provided answers is needed.

Maybe you could use JOIN to collect all text in one field:

https://help.smartsheet.com/function/join

If the data in your input sheet with the form can be divided into sections (like "technical", "organisational"...) you could use a text type column (not showing up in the form) for every section. In that cell the JOIN formula collects only data from the section and in your sheet B you would only show these summary cells.

Hope this helps

Stefan

Taylor Riley

Thanks, @Andrée Starå and @Stefan,

I used function: =JOIN(COLLECT([Type of Change - Obill]@row:[Please Schedule GTM Office Hours]@row, [Type of Change - Obill]@row:[Please Schedule GTM Office Hours]@row, NOT(ISBLANK(@cell))), " - ")

and my Summary cell now looks like: New Offer - Monthly + Annual - Buy Now - Percentage Discount - 0.5 - 08/03/20 - 08/17/20 - Direct - Retail - Yes - Web - Yes - Product - Sku's

This is getting closer to what I need, but the way the information is presented can still be a little confusing. Is there a way I can label what information is showing and also have the information separated by a return. Something like this:

Request Type:

New Offer

Frequency:

Monthly + Annual

Discount Type:

Percentage

Etc.

Andrée Starå

@Taylor Riley

You're more than welcome!

Yes, it's possible, but not with bold text.

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Andrée Starå If you are able to come up with something, could you share it?

I have a few different ideas using a helper row to replicate the column names and then some INDEX/COLLECT formulas maybe to consolidate/present the info, but I haven't tested anything yet. I am wondering if your idea(s) may be more efficient or easier to manage.

Andrée Starå

@Paul Newcome

That was my thought as well, and combine it with CHAR(10) to separate them.

BenG7

@Andrée Starå @Paul Newcome I'm fairly new to Smartsheet but have this exact same issue. I know this is an old thread, but would it be possible to share an example sheet that functions as mentioned above (consolidating only the filled columns for a specific row item into one column, with the data separated by returns and including the column titles)? I have not worked with INDEX/COLLECT/JOIN before so an example would be very helpful.

@Taylor Riley did you get this to work?

Thanks everyone!😃

Ben

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.