Creating a Query Function on a Dashboard

MarianneD617

Is there a way to create a query that can be accessed via a dashboard to return data from a set of sheets? Here is my use case:

Sheet 1 includes customer profile and demographic data

Sheet 2 includes customer call history (date, topics discussed, etc.)

An Executive Level Dashboard includes reports that pull from the two sheets.

We want to be able to query by customer name and selected fields drawing from the two sheets. For example: Is the customer under contract, where are they located, contact info, and what is the call history.

I know I can set up a report and filter on the customer name. I'm looking for a simpler approach for the user to just plug in the name and fields of interest to see the data they're looking for.

Thank you!!

Mike TV

@MarianneD617

I think you could do it with a sheet that index matches all of the columns you'd want to pull in information from. Then put that sheet on your dashboard where the users enter their search criteria into one or more fields. That should work. Let me know if you need help setting it up or need to see an example.

MarianneD617

Could you show me an example? Thanks so much!

Mike TV

@MarianneD617

First I created a simple data sheet. You won't need to create one because you've already got one. It's the one you want to query data out of. Here's my example:

Then you create a sheet used to Query the other sheet with. Here's the one I created:

I've got Row 1 locked so only myself or Admins of the Query sheet can edit anything on that row. It also lets someone know where to enter their search info.

The cell in row 2 of the Address column has this formula:

=IFERROR(INDEX({Data Sheet Address}, MATCH(Name@row, {Data Sheet Name}, 0)), "Not Found")

The cell in row 2 of the Phone # column has this formula:

=IFERROR(INDEX({Data Sheet Phone #}, MATCH(Name@row, {Data Sheet Name}, 0)), "Not Found")

The cell in row 2 of the Age column has this formula:

=IFERROR(INDEX({Data Sheet Age}, MATCH(Name@row, {Data Sheet Name}, 0)), "Not Found")

The cell in row 2 of the Relationship Status has this formula:

=IFERROR(INDEX({Data Sheet Relationship Status}, MATCH(Name@row, {Data Sheet Name}, 0)), "Not Found")

Then you have to Publish the sheet so it can be used as Web Content on a Dashboard. You Publish with this icon on the right sidebar:

Then you turn on Edit by Anyone like so:

Then you copy the link URL from here when it pops up after turning the Edit by Anyone on:

Then you create a dashboard with a Web Content widget linking to your Query Sheet like so:

That's a result when I put in a name that's in the Data Sheet. Here's a result when I enter a name that isnt:

Mike TV

@MarianneD617

Also since you may want to query against multiple criteria than just a single value it sounded like in your post, you'll want to switch the formulas to an INDEX(COLLECT( formula. That will allow you to specify multiple criteria to search against.

MarianneD617

This is great! I really appreciate your help and will give it a try.

Thank you!!

Mike TV

@MarianneD617

One thing I forgot to mention. On the Query Sheet I created a column which I hid and called it Show that's a checkbox column. I checked the box in Row 2 before hiding the column. That way I was able to enter formulas in those row 2 cells. You'll have to type something temporarily in the Name column so that the checkbox in the Show column is available to be checked but once it's checked you can remove the text in the Name column and then fill in your formulas.

MarianneD617

Got it! thanks again!

Marcelle Santos

Is it possible to have this result for multiple searches? I would like to use the same logic but for a list of customer names.

mwhite24

How were you able to cross-reference the data? I keep receiving "Not Found" error