A single sheet that can pull data requested from sheet with Form responses

Hi,

The end goal is a single sheet where someone can input something to pull from another sheet that was populated by form responses.

Example using ice cream:

Personnel completes form that provides how much they like different types of ice cream. And they use Harvey balls to select how much they like the ice cream.

I want a separate sheet where I can query things like "who liked chocolate ice cream the most?" and "Who liked strawberry ice cream and vanilla ice cream the least?"

It would look at the columns in the sheet that contains form responses, and return with person and what Harvey ball they used to rate the ice cream.

I'd like to avoid using filters to do this. I want it to be as "clean" as possible for the person using the sheet. I'm thinking VLOOKUP formula, but I don't feel comfortable enough doing this without help. I've read the help documents, but have not much luck getting it to work on my own.

Can it be done? and any suggestions how to do it?

Find more posts tagged with

Accepted answers

All comments

Andrée Starå

Hi @Matt K

Will all the queries be set from start?

I hope that helps!

Be safe and have a fantastic week!

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!

Matt K

Thanks for the reply, @Andrée Starå. No. The queries should be dynamic. Sticking with the ice cream analogy....If I'm Baskin Robbins and have 31 flavors, I can't build queries from the start that include 3 unique flavors out of 31 since those 3 might change based on who is asking. I might want to know how much a person liked dark chocolate AND Caramel swirl AND French Vanilla, the next time I want to know how much a person likes Caramel swirl AND Bubblegum AND Coffee.

Matt K

Has anyone else been able to do similar?

Genevieve P.

Hi @Matt K

If I understand you correctly, you want a sheet that has two fields that someone can fill in to then update/populate the third field with the answer, based on those two criteria.

Example Second Sheet:

Screen Shot 2020-10-07 at 4.18.09 PM.png

^In this scenario, the formula is in the Status Ball "Like?" column, and the Person Name and Flavour columns can be populated to see what the response was.

Here is the source sheet, where the information would have been pulled in from the form:

Screen Shot 2020-10-07 at 4.19.17 PM.png

You will note that in order to send in different information, I would have had to submit the form twice (once for all the ones that I liked, once for all the yellow ones, etc).

The formula I used in the second, data sheet is as follows:

=INDEX(COLLECT({Harvey Ball Range}, {Person Range}, [Person Name]@row, {Flavour Range}, HAS(@cell, Flavour@row)), 1)

The COLLECT function filters down what rows the first column listed is looking for. Then the INDEX function returns the one that matches those criteria. First you list the range you want returned (the Harvey Ball range). Then you list your column with the criteria, and reference the cell in the current sheet that will contain this criteria, using @row.

This will allow someone to change what's in the Person Name column and the Flavour column to see different Harvey Ball results.

You'll notice I also used the HAS function because in my example I'm using a multi-select column in the sheet where the form submissions come in.

Screen Shot 2020-10-07 at 4.20.35 PM.png

Let me know if I've understood what you're looking to do or not!

Cheers,

Genevieve

David Tutwiler

This is a pretty interesting use case. But this sounds like a job for Report Builder, not a sheet. Let me explain.

The way I understand it is that you'll have the user fill out the form with the various combinations of things (flavors if we're sticking with the ice cream example) and you want to be able to find out who rated a certain flavor (or flavor combination) the highest.

If it were me, I would let the input sheet stay as is (I'm assuming a multi-select column with your various combination options) and build a report, or set of reports on top of that. Whoever is doing your research will simply need to change the "What" section to "Contains" and then whatever you search parameter is (ie Vanilla). You could set your report to automatically sort by the review, so you could not only see who rated it the highest, but who gave ratings with that flavor in their combination. To me it would be easier to manipulate than a sheet formula.

Would that work for what you're looking for?

Matt K

@Genevieve P @David Tutwiler I need to review, digest, and test based on both responses and will reply. Thank you for the suggestions!

Andrée Starå

@Matt K

Happy to help!

I saw that it got answered already!

Let me know if I can help with anything else!

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