Using a series of named ranges in SUMPRODUCT

[JzP]

Hi all,
For various complicated reasons I am using a macro to build a series
of "helper" named ranges which are named "Row" and then the row
number. (ie Row1, Row2 etc)
I can then use the following function in my worksheet;
=SUMPRODUCT(TRANSPOSE(Row1)*(rngDataNums)) and, if I enter it with
Ctrl-Shift-Enter, I get the correct value.

What I want to do is to replace the "Row1" in the formula above with
the equivalent of "Row" & Row() so each row will use the correct named
range previously built for that row.

However, can't work out the correct syntax.

Any help would be greatly appreciated.
TIA

John

 

[Toppers]

Try:

=SUMPRODUCT(TRANSPOSE(INDIRECT("row"&ROW())*(rngDataNums)))

 

[JzP]

Try:

=SUMPRODUCT(TRANSPOSE(INDIRECT("row"&ROW())*(rngDataNums)))









- Show quoted text -

Thanks Toppers,

Unfortunately that gives a "#REF" error.

John

 

[Toppers]

You will get #REF if ROWn does not exits.

Can you give an example of (say) row1 and "rngDataNums" (ranges and data)

 

[JzP]

You will get #REF if ROWn does not exits.

Can you give an example of (say) row1 and "rngDataNums" (ranges and data)








- Show quoted text -

The Data sheet contains 4 columns of data.
rngData is:
colA colB colC
a 1 Y
a 2 N
a 3 Y
a 1 N
a 2 Y
b 1 N
b 1 N
b 2 N
b 3 N
a 1 Y
b 2 Y
c 3 N
c 1 Y
c 2 N
c 3 Y
c 1 N
c 2 Y

rngDataNums is
colD
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17


On Sheet1 the first two rows are:
colA colB colC
a 1 Y
b 1 N

The vba code which creates the arrays is:
Sub Test_BuildMatchList()
BuildMatchListArray3 "rngData", 1, "$A$1", "$B$1", "$C$1"
BuildMatchListArray3 "rngData", 2, "$A$2", "$B$2", "$C$2"
End Sub

Function BuildMatchListArray3(ByVal vaIn As Variant, ByVal intRowNo As
Integer, ByVal strA1 As String, ByVal strA2 As String, _
ByVal strA3 As String)


Dim Arr() As Boolean
Dim intCount As Integer
Dim intRow As Integer
Dim intCol As Integer
Dim arstr(3) As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim myRange As Range

arstr(1) = Range(strA1).Value
arstr(2) = Range(strA2).Value
arstr(3) = Range(strA3).Value

ReDim Arr(16)
If Not IsMissing(vaIn) Then
Set myRange = Range(vaIn)
'Fill the array with 1 or 0 depending on whether there's a match
across the columns
For intRow = 1 To myRange.Rows.Count
If myRange.Offset(intRow, 1).Cells(0, 0) = arstr(1) Then
If myRange.Offset(intRow, 2).Cells(0, 0) = arstr(2) Then
If myRange.Offset(intRow, 3).Cells(0, 0) = arstr(3) Then
Arr(intRow - 1) = 1
Else
Arr(intRow - 1) = 0
End If
Else
Arr(intRow - 1) = 0
End If
Else
Arr(intRow - 1) = 0

End If
Next intRow
End If

For intCount = 0 To 16
Debug.Print Arr(intCount)
Next intCount

Names.Add Name:="Row" & intRowNo, RefersTo:=Arr

End Function

Then on Sheet1 the formula will go into column D. Once it works I need
to put it into 104 columns across and match on 8 data columns which is
why I'm trying to avoid doing the Sumproduct lookup for each cell. If
I can do the match and get the array of matching attribute rows once
per sheet1 row I'm hoping it'll speed things up a lot.

If I use =SUMPRODUCT(TRANSPOSE(Row1)*(rngDataNums)) in D1 I get 11
which is correct.
if i use =SUMPRODUCT(TRANSPOSE(Row2)*(rngDataNums)) in D2 I get 13
which is correct.

Hope that's clearer.

Thanks
John

 

[Toppers]

John,
Sorry but I honestly don't know as "ROWn" are arrays rather than
cell ranges.

 

[JzP]

John,
Sorry but I honestly don't know as "ROWn" are arrays rather than
cell ranges.




















- Show quoted text -

Hi again ,
FYI if anyone's interested, I have come up with a workaround (ish).
If I create a two dimensional array (called RowData) which covers all
the rows I'm interested in I can then use an index into that array
based on the row number I'm looking for.

thus I end up with
=SUMPRODUCT(TRANSPOSE(INDEX(RowData,ROW(),))*(rngDataNums)) in my
sheet and that can fill down.

I'm still optimising the macro which creates the array and will post
it if anyone cares.

Cheers and thanks for reading this far

John

 

[Toppers]

Good thinking!

Hi again ,
FYI if anyone's interested, I have come up with a workaround (ish).
If I create a two dimensional array (called RowData) which covers all
the rows I'm interested in I can then use an index into that array
based on the row number I'm looking for.

thus I end up with
=SUMPRODUCT(TRANSPOSE(INDEX(RowData,ROW(),))*(rngDataNums)) in my
sheet and that can fill down.

I'm still optimising the macro which creates the array and will post
it if anyone cares.

Cheers and thanks for reading this far

John