Using a series of named ranges in SUMPRODUCT
 

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

Using a series of named ranges in SUMPRODUCT
 

Try:

=SUMPRODUCT(TRANSPOSE(INDIRECT("row"&ROW())*(rngDa taNums)))



"JzP" wrote:

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

Using a series of named ranges in SUMPRODUCT
 

On Jun 18, 10:55 am, Toppers
wrote:
Try:

=SUMPRODUCT(TRANSPOSE(INDIRECT("row"&ROW())*(rngDa taNums)))



"JzP" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Toppers,

Unfortunately that gives a "#REF" error.

John

Using a series of named ranges in SUMPRODUCT
 

You will get #REF if ROWn does not exits.

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

"JzP" wrote:

On Jun 18, 10:55 am, Toppers
wrote:
Try:

=SUMPRODUCT(TRANSPOSE(INDIRECT("row"&ROW())*(rngDa taNums)))



"JzP" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Toppers,

Unfortunately that gives a "#REF" error.

John

Using a series of named ranges in SUMPRODUCT
 

On Jun 18, 11:25 am, Toppers
wrote:
You will get #REF if ROWn does not exits.

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



"JzP" wrote:
On Jun 18, 10:55 am, Toppers
wrote:
Try:

=SUMPRODUCT(TRANSPOSE(INDIRECT("row"&ROW())*(rngDa taNums)))

"JzP" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Toppers,

Unfortunately that gives a "#REF" error.

John- Hide quoted text -

- 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 a
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

Using a series of named ranges in SUMPRODUCT
 

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

"JzP" wrote:

On Jun 18, 11:25 am, Toppers
wrote:
You will get #REF if ROWn does not exits.

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



"JzP" wrote:
On Jun 18, 10:55 am, Toppers
wrote:
Try:

=SUMPRODUCT(TRANSPOSE(INDIRECT("row"&ROW())*(rngDa taNums)))

"JzP" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Toppers,

Unfortunately that gives a "#REF" error.

John- Hide quoted text -

- 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 a
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

Using a series of named ranges in SUMPRODUCT
 

On Jun 18, 1:04 pm, Toppers wrote:
John,
Sorry but I honestly don't know as "ROWn" are arrays rather than
cell ranges.



"JzP" wrote:
On Jun 18, 11:25 am, Toppers
wrote:
You will get #REF if ROWn does not exits.

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

"JzP" wrote:
On Jun 18, 10:55 am, Toppers
wrote:
Try:

=SUMPRODUCT(TRANSPOSE(INDIRECT("row"&ROW())*(rngDa taNums)))

"JzP" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Toppers,

Unfortunately that gives a "#REF" error.

John- Hide quoted text -

- 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 a
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- Hide quoted text -

- 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(),))*(rngD ataNums)) 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

Using a series of named ranges in SUMPRODUCT
 

Good thinking!

"JzP" wrote:

On Jun 18, 1:04 pm, Toppers wrote:
John,
Sorry but I honestly don't know as "ROWn" are arrays rather than
cell ranges.



"JzP" wrote:
On Jun 18, 11:25 am, Toppers
wrote:
You will get #REF if ROWn does not exits.

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

"JzP" wrote:
On Jun 18, 10:55 am, Toppers
wrote:
Try:

=SUMPRODUCT(TRANSPOSE(INDIRECT("row"&ROW())*(rngDa taNums)))

"JzP" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Toppers,

Unfortunately that gives a "#REF" error.

John- Hide quoted text -

- 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 a
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- Hide quoted text -

- 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(),))*(rngD ataNums)) 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