Hi Bernie
It works when I insert a row in Blank 1, but I'm sorry my workbook is a lot
more complicated than that. Maybe I should have explained earlier. Blank 1
and Blank 2 are like 'bookends', they will eventually be hidden from the
user. As I am designing a template, there will be a variable number of
worksheets in between these 2 bookends. Therefore there will also be a
different number of rows on each worksheet. Each worksheet starts out the
same when inserted, as this is done using a macro to standardise formatting.
At the moment the formulas work when more worksheets are entered, the problem
will come when the user will insert more rows.
Sorry to complicate the problem,
Thanks
"Bernie Deitrick" wrote:
Joanne,
You need to set up the formula before you insert the row between A9 and A10 on sheet "Blank 1"
The formula should update from
=CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" &
ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31)
to
=CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" &
ADDRESS(ROW('Blank 1'!A11),COLUMN('Blank 1'!A11));A31)
HTH,
Bernie
MS Excel MVP
"Joanne" wrote in message
...
No joy I'm afraid, this formula continues to only count within rows 9 and 10.
I know thats it's possible to do this, because with normal 'Countif' 2d it
extends the range when a row is inserted.
Any other suggestions?
Thanks
Joanne
"Bernie Deitrick" wrote:
The function takes a string as the address, so you would need to create the string. Perhaps,
=CountIf3D2("'Blank 1:Blank 2'!" & ADDRESS(ROW('Blank 1'!A9),COLUMN('Blank 1'!A9)) & ":" &
ADDRESS(ROW('Blank 1'!A10),COLUMN('Blank 1'!A10));A31)
HTH,
Bernie
MS Excel MVP
"Joanne" wrote in message
...
Sorry I now have another issue with this formula:
When I insert rows on the worksheets that the function is searching, the
range in the formula does not expand like they normally do.
I even tried using the "$" sign but it didn't work.
Formula: =CountIf3D2("'Blank 1:Blank 2'!$A$9:$A$10";A31)
or for Sumproduct: =SumProduct3D2C("'Blank 1:Blank
2'!$A$9:$A$10";A31;"'Blank 1:Blank 2'!$J$9:$J$10";"x")
If I insert a row in eg Blank 1, even if I insert in between rows 9 and 10,
it stays the same. It does not count the values in row 11.
Thanks in advance
"Bernie Deitrick" wrote:
Joanne,
The function, as written, simply multiplies two ranges together and then sums. It does not
handle
the comparison automatically.
It can be re-written to do so - try this version and use it like
=SumProduct3D2C("'Blank 1:Blank 2'!A9:A10",A45,"'Blank 1:Blank 2'!J9:J10","x")
Function SumProduct3D2C(sRng1 As String, Val1 As Variant, _
sRng2 As String, Val2 As Variant) _
As Variant
Dim vaRng1 As Variant, vaRng2 As Variant
Dim rTemp As Range
Dim i As Long
Dim Sum As Double
Dim rCell As Range
Application.Volatile
vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1)
vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2)
For i = LBound(vaRng1) To UBound(vaRng1)
Sum = Sum + (vaRng1(i).Value = Val1) * (vaRng2(i).Value = Val2)
Next i
SumProduct3D2C = Sum
End Function
HTH,
Bernie
MS Excel MVP
"Joanne" wrote in message
...
Further to a post yesterday looking at Countif3d, I am now trying to use a
similar UDF for SumProduct 3d, but I keep getting the #Value! I found the
macro on:
http://www.dailydoseofexcel.com/arch...ned-functions/
(it has been modified a bit from the website based on a suggestion from the
forum yesterday - which worked perfectly for countif3d)
What am I doing wrong? Thank you in advance
The formula I enter is:
=SumProduct3D2(("'Blank 1:Blank 2'!A9:A10"=A45)*("'Blank 1:Blank
2'!J9:J10"="x"))
The macro for the function is:
Function SumProduct3D2(sRng1 As String, sRng2 As String) _
As Variant
Dim vaRng1 As Variant, vaRng2 As Variant
Dim rTemp As Range
Dim i As Long
Dim Sum As Double
Dim rCell As Range
Application.Volatile
vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, sRng1)
vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, sRng2)
For i = LBound(vaRng1) To UBound(vaRng1)
Sum = Sum + (vaRng1(i).Value * vaRng2(i).Value)
Next i
SumProduct3D2 = Sum
End Function
Function Parse3DRange2(wb As Workbook, _
SheetsAndRange As String) As Variant
Dim sTemp As String
Dim i As Long, j As Long
Dim Sheet1 As String, Sheet2 As String
Dim aRange() As Range
Dim sRange As String
Dim lFirstSht As Long, lLastSht As Long
Dim rCell As Range
Dim rTemp As Range
On Error GoTo Parse3DRangeError
sTemp = SheetsAndRange
'if it's 3d, rtemp will be nothing
On Error Resume Next
Set rTemp = Range(sTemp)
On Error GoTo Parse3DRangeError
'if it's 3d, parse it
If rTemp Is Nothing Then
i = InStr(sTemp, "!")
If i = 0 Then Err.Raise 9999
'next line will generate an error if range is invalid
'if it's OK, it will be converted to absolute form
sRange = Range(Mid$(sTemp, i + 1)).Address
sTemp = Left$(sTemp, i - 1)
i = InStr(sTemp, ":")
Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "")
If i 0 Then
Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "")
Else
Sheet1 = Sheet2
End If
'next lines will generate errors if sheet names are invalid
With wb
lFirstSht = .Worksheets(Sheet1).Index
lLastSht = .Worksheets(Sheet2).Index
'swap if out of order
If lFirstSht lLastSht Then
i = lFirstSht
lFirstSht = lLastSht
lLastSht = i
End If
'load each cell into an array
j = 0
For i = lFirstSht To lLastSht
For Each rCell In .Sheets(i).Range(sRange)
ReDim Preserve aRange(0 To j)
Set aRange(j) = rCell
j = j + 1
Next rCell
Next i
End With
Parse3DRange2 = aRange
Else
'range isn't 3d, so just load each cell into array
For Each rCell In rTemp.Cells
ReDim Preserve aRange(0 To j)
Set aRange(j) = rCell
j = j + 1
Next rCell
Parse3DRange2 = aRange
End If
Parse3DRangeError:
On Error GoTo 0
Exit Function
End Function 'Parse3DRange