I have a UDF (see bottom) which finds a particular col header then looks up a
range in that column. I want to pass this range to a sumproduct formula in
another cell:

=SUMPRODUCT(((TRIM($A$8:$A46)="A")+(TRIM($A$8:$A46 )="B")+(TRIM($A$8:$A46)="C")+(TRIM($A$8:$A46)="D") )*((TRIM($B$8:$B46)="E")),C$8:C46)

So for the above, C$8:C46 would be replaced by REFR()

REFR works fine, returning the correct range reference, but when i put it in
the sumproduct formula a value error results.

Please help

Thanks

Function REFR(Product As Range, Colhead As Range)

Application.Volatile

Dim c As Range
Dim productstring As String
Dim cs As Integer
Dim rs As Integer
Dim sumthese As Variant

productstring = Product.Value


For Each c In Colhead.Cells

If Trim(c) = Trim(productstring) Then

cs = c.Column
rs = c.Row + 1

Set sumthese = Range(Cells(rs, cs), Cells(rs + 35, cs))

Else: End If

Next c

REFR = sumthese.Address(rowabsolute:=False, columnabsolute:=False)

End Function

Untested:

=SUMPRODUCT(((TRIM($A$8:$A46)="A")
+(TRIM($A$8:$A46)="B")
+(TRIM($A$8:$A46)="C")
+(TRIM($A$8:$A46)="D"))
*((TRIM($B$8:$B46)="E")),
indirect(refr(rng1,rng2)))

(change rng1 and rng2 to the addresses you need)



PBcorn wrote:

I have a UDF (see bottom) which finds a particular col header then looks up a
range in that column. I want to pass this range to a sumproduct formula in
another cell:

=SUMPRODUCT(((TRIM($A$8:$A46)="A")+(TRIM($A$8:$A46 )="B")+(TRIM($A$8:$A46)="C")+(TRIM($A$8:$A46)="D") )*((TRIM($B$8:$B46)="E")),C$8:C46)

So for the above, C$8:C46 would be replaced by REFR()

REFR works fine, returning the correct range reference, but when i put it in
the sumproduct formula a value error results.

Please help

Thanks

Function REFR(Product As Range, Colhead As Range)

Application.Volatile

Dim c As Range
Dim productstring As String
Dim cs As Integer
Dim rs As Integer
Dim sumthese As Variant

productstring = Product.Value

For Each c In Colhead.Cells

If Trim(c) = Trim(productstring) Then

cs = c.Column
rs = c.Row + 1

Set sumthese = Range(Cells(rs, cs), Cells(rs + 35, cs))

Else: End If

Next c

REFR = sumthese.Address(rowabsolute:=False, columnabsolute:=False)

End Function

--

Dave Peterson