Report based on specific text in a cell

Devan Hutchens

Hello,

I have a data sheet used for a call log for project issues.  I'd like to be able to run a report that displays all calls for a specific serial number based on which serial number is entered into a cell, a variable search value.  Ideally, I would have a dashboard with a web content widget that would be looking at a sheet.  The user would be able to input the project serial number, save and refresh the dashboard.  A report would be below the web content widget with all entries for that serial number.  I realize the user could go directly to the sheet and filter the project number, however, I don't want multiple users filtering and sorting data at the same time to avoid potential issues.

Andrée Starå

Hi Devan,

I think I have a solution for this. I'll get back to the post when I find it.

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Devan Hutchens

Awesome!  Looking forward to seeing your solution!

Paul Newcome

I would think that having the user input into the sheet then using that entry to generate a report on a shared dashboard would run into the same issues as using filters on the sheet.

 

How many different serial numbers do you have, and is that number expected to change?

Andrée Starå

Unfortunately, it won't work because of the same reason that Paul commented on below.

How many serial numbers and users will there approximately be?

Devan Hutchens

Currently there are about 3,000.  As we close projects, this list will continue to grow.  The dashboard would not be shared, it would be for a specific user.

Andrée Starå

Ok.

How many columns would need to be included in the sheet/report?

Devan Hutchens

No more than 10.

Andrée Starå

One more question!

How many rows could there be for each serial number?

Devan Hutchens

Hi Andree,

There could be as few as 1 or as many as 50.

Andrée Starå

Ok.

I have two alternative solutions.

1. In the serial number column or a so-called helper column, we add a top row where we input the serial number we want to show in the report. In a column, we then have the formula to check a box if the serial number in the top matches the one on the row. Criteria in the report would be to show rows that have a checked box.
2. Create another sheet and using cross-sheet formulas instead. 

Would that work?

Which method do you prefer?