Best formula to return a list of qualifications for card view

Hi Community,

So, I am trying to save myself a lot of manual data entry. Ultimately, I am trying to take a spreadsheet that was created in Excel, to capture each persons "qualifications" in a specific field, and get it into card view. I have attached the files of what I am starting with and what I would like to end up with. The issue is I don't want to manually create a separate entry for each person and their qualification in order to populate the card data. I was hoping to do a sort of If/And formula to return the column headers that have an X in the column and then maybe copy past to my data sheet. There are about 30 column headers and about 110 people.

I was thinking maybe something along the lines of the above formula, however, I realize that would be a lot of individual formulas to create doing it that way. Plus the fact that I wasn't able to get that one to work. I am somewhat new to card view so if there is a better way please let me know! I am open to suggestions.

Thank you!

Shesha

Find more posts tagged with

Formulas

functionality

Accepted answers

L_123

There are a couple errors in your copy of the equation, but let's disregard that. I've put a little more thought into the solution, and I believe you will run out of space on this if you follow my previous solution and stacked it.

Does the output have to be on another sheet? can it be on the same sheet then pulled into a report?

L_123

https://app.smartsheet.com/b/publish?EQBCT=881a5170647c442bacdb6980a16dd417

I put together a little sheet for you. The formulas are fairly complicated, but each unique formula is highlighted in a different color.

All comments

L_123

=IF(CONTAINS([Primary Column]@row, JOIN(COLLECT({If/When Qualifications Area of Expertise}, {If/When Qualifications SME}, [Primary Column]@row))), "X")

You will need to edit the other sheet references to the correct columns. I also recommend naming all other sheet references, it can be a real pain to understand your formulas when coming back to them in the future, speaking from past experience.

Shesha_K

So I am getting #INCORRECT ARGUMENT when I try that formula. I have attached images showing which columns I am referencing. The column headers are the "areas of expertise".

I am not sure if that is what you meant. I am trying to return something that looks like this:

L_123

Does the output have to be on another sheet? can it be on the same sheet then pulled into a report?

L_123

https://app.smartsheet.com/b/publish?EQBCT=881a5170647c442bacdb6980a16dd417

I put together a little sheet for you. The formulas are fairly complicated, but each unique formula is highlighted in a different color.

Shesha_K

This will work so much better than what I was trying! Thank you so much for your help!

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.