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
Best Answers
-
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?
-
I put together a little sheet for you. The formulas are fairly complicated, but each unique formula is highlighted in a different color.
Answers
-
=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.
-
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:
-
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?
-
I put together a little sheet for you. The formulas are fairly complicated, but each unique formula is highlighted in a different color.
-
This will work so much better than what I was trying! Thank you so much for your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!