Data Validation - Find and Replace? (Dashboard)

[smithj96]

So I'm not 100% sure if Data Validation is the way to go about this or if there's an easier way to accomplish what I'm trying to do at all. Essentially, I'm trying to build somewhat of a custom dashboard where I'm able to select a name from the dropdown, and populate the metrics below associated with that name. Hard part is, this data is all based off of seven yes/no questions so it's not a simple math formula. Yes being the positive, no being the negative. This is what I have set up so far for the beginning stage of this dashboard (the empty space to the right of the percentages is where I'd like to put total averages month over month for the name selected in a line graph, right now it's just calculating ALL totals in my workbook).

[TABLE="width: 781"]
<tbody>[TR]
[TD="colspan: 4"]Bob[/TD]
[TD][/TD]
[TD="colspan: 7"]57% Total Avg.[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Total Avg. per Question[/TD]
[TD][/TD]
[TD="colspan: 8"]

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Question 1:[/TD]
[TD]33%[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Question 2:[/TD]
[TD]67%[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Question 3:[/TD]
[TD]33%[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Question 4:[/TD]
[TD]67%[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Question 5:[/TD]
[TD]67%[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Question 6:[/TD]
[TD]67%[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Question 7:[/TD]
[TD]67%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Right now, the cells with percentages are populating with this formula: =COUNTIFS(Sheet18!A:A,"Bob",Sheet18!C:C,"Yes")/COUNTIF(Sheet18!A:A,"Bob"). The data is being grabbed from another raw data sheet. The "Total Avg" cell is simply just averaging the column of per question averages.

The cell with "Bob" is obviously where the data validation is with a list of other names. I'm relatively new to data validation so I'm assuming there's a way to make this happen; but again, being new to this feature, I may be a tad over-ambitious. Any help is greatly appreciate. Please don't be afraid to call me names and tell me I'm insane if that happens to be the case here.

**I'm trying to attach a sample workbook to this message but cant seem to find the option. Any hints?

 

[Alan_P]

Hi SmithJ96,

You'd need to use a website like DropBox or similar to share a copy of your workbook...

Cheers,
Alan.

 

[smithj96]

Hi Alan_P,

Thanks that would make too much sense. Here we go:

ExpireBox