Hello all! It's my first post, so please be nice
I'm trying to create a "dashboard" which looks up a value (hlookup) in the top row of the data set and returns back a sum of its "scores" from the column to the right. I tried and failed at nesting hlookup, sumproduct, and wrapping them in an offset. Help please?
[TABLE="width: 434"]
<colgroup><col width="62" span="7" style="width:47pt"> </colgroup><tbody>[TR]
[TD="class: xl69, width: 62"] [/TD]
[TD="class: xl69, width: 62"]bob[/TD]
[TD="class: xl69, width: 62"]score[/TD]
[TD="class: xl69, width: 62"]mary[/TD]
[TD="class: xl69, width: 62"]score[/TD]
[TD="class: xl69, width: 62"]jean[/TD]
[TD="class: xl69, width: 62"]score[/TD]
[/TR]
[TR]
[TD="class: xl69"]q1[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]3[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]q2[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]2[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]3[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]q3[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]0[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]3[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]2[/TD]
[/TR]
[TR]
[TD="class: xl69"]q4[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, colspan: 3"]dashboard[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"] [/TD]
[TD="class: xl68"]q1+2[/TD]
[TD="class: xl68"]q3+4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"]bob[/TD]
[TD="class: xl68"]3[/TD]
[TD="class: xl68"]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"]mary[/TD]
[TD="class: xl68"]?[/TD]
[TD="class: xl68"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"]jean[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'm trying to create a "dashboard" which looks up a value (hlookup) in the top row of the data set and returns back a sum of its "scores" from the column to the right. I tried and failed at nesting hlookup, sumproduct, and wrapping them in an offset. Help please?
[TABLE="width: 434"]
<colgroup><col width="62" span="7" style="width:47pt"> </colgroup><tbody>[TR]
[TD="class: xl69, width: 62"] [/TD]
[TD="class: xl69, width: 62"]bob[/TD]
[TD="class: xl69, width: 62"]score[/TD]
[TD="class: xl69, width: 62"]mary[/TD]
[TD="class: xl69, width: 62"]score[/TD]
[TD="class: xl69, width: 62"]jean[/TD]
[TD="class: xl69, width: 62"]score[/TD]
[/TR]
[TR]
[TD="class: xl69"]q1[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]3[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]q2[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]2[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]3[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]q3[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]0[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]3[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]2[/TD]
[/TR]
[TR]
[TD="class: xl69"]q4[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]1[/TD]
[TD="class: xl69"]xxx[/TD]
[TD="class: xl69"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, colspan: 3"]dashboard[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"] [/TD]
[TD="class: xl68"]q1+2[/TD]
[TD="class: xl68"]q3+4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"]bob[/TD]
[TD="class: xl68"]3[/TD]
[TD="class: xl68"]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"]mary[/TD]
[TD="class: xl68"]?[/TD]
[TD="class: xl68"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"]jean[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]