SUMPRODUCT: Help with text #VALUE, and INDIRECT(SUMPRODUCT)

[rasonline]

Hi guys

Need help with 2 problems using sumproduct:
1. COnstantly getting #VALUE error no matter what formula method is used
2. Possible to use INDIRECT formula to create a SUMPRODUCT?

I've attached a workbook.
Using Excel 2007.

Problem 1:
I have the following sumproduct formula which works fine as it stands:

=SUMPRODUCT(--(E$6='2'!$C$2)*--(VALUE($D8)=VALUE(TRIM('2'!$A$3:$A$253))),('2'!$C$3:$C$253))

However, if i extend the row references to beyond row 253, then I get the #VALUE error. There are text entries in row 254 onwards.

I've tried various combinations with the unaries, using commas instead of " * ", etc but simply cannot get it to work no matter what I do.

Ideally I would like the formula to read across these columns and rows:
=SUMPRODUCT(--(E$6='2'!$C$2:$BA$2)*--(VALUE($D9)=VALUE(TRIM('2'!$A$3:$A$500))),('2'!$C$3:$BA$500)))

Any suggestions?

Problem 2:
You will see in the attached workbook (yellow cells) that I need to make the formula references to columns-on-Sheet2 dynamic. I am able to identify the column numbers to which the SUMPRODUCT should refer to.

Using A1 notation, I have no clue how to get this to work.
(Due to workbook structure, I cannot use a VLOOKUP of column labels vs column numbers i.e. cannot create list of A-Z with corresponding column numbers.)

So I then changed to R1C1 notation, AND tried to use INDIRECT formula to make the column references dynamic.

The formula in A1 notation: =SUMPRODUCT(--(E$6='2'!$C$2)*--(VALUE($D8)=VALUE(TRIM('2'!$A$3:$A$253))),('2'!$C$3:$C$253))

The formula in R1C1 notation (excel converted): =SUMPRODUCT(--(R6C='2'!R2C3)*--(VALUE(RC4)=VALUE(TRIM('2'!R3C1:R253C1))),('2'!R3C3:R253C3))

Then using INDIRECT, i substituted the column references with the dynamic references (manually typed in):

=INDIRECT("SUMPRODUCT(--(R6C='2'!R2C"&R2C&")*--(VALUE(RC4)=VALUE(TRIM('2'!R3C"&R1C&":R253C"&R1C&"))),('2'!R3C"&R2C&":R253C"&r2c&"))")

However, this results in #REF.

Am really at my wits end with this one. Any help appreciated, or even alternative suggested approaches to achieving the end result.

Thanks.