Another Dynamic Range problem

[jmiaebrown]

Hi. I have a dynamic range issue.
I have defined a dynamic range name TCMStartRow as
=OFFSET('Test Case Matrix'!$A$7,0,0,COUNTA('Test Case Matrix'!$A:$A),1

The row header is on $A$7 and the end is unknown. On another sheet I defined a formula to count the number of columns with text equal to another column

=COUNTIF(TCMStartRow,$A10)

So if $A10="Module1" within the TCMStartRow dynamic range then we should count it.

This seems to work somewhat but it does not seem to extend all the way down the column. I think maybe because of the empty cells that I have in ColumnA.

Have I defined the dynamic range incorrectly?

R/C	A​	B​	C​	D​	E​	F​	G​
52	Module1		1	Do Test Case1		1	Do Step1
53						2	Do Step2
54						3	Do Step3
55	Module1		2	Do Test Case2		1	Do Step1
56						2	Do Step2
57						3	Do Step3
58	Module2					1	Do Step1
59			3	Do Test Case3

 

[mikerickson]

You could adjust the third argument to account for blanks

=OFFSET($A$7,0,0,COUNTA($A:$A)-COUNTA($A$1:$A$6),1)

If there are blanks below A7, futhrer adjustment would be needed.

 

[DonkeyOte]

If Mike's formula does not fix it for you can you elaborate on the data type in A -- is it numeric or text ?

 

[jmiaebrown]

You could adjust the third argument to account for blanks

=OFFSET($A$7,0,0,COUNTA($A:$A)-COUNTA($A$1:$A$6),1)

If there are blanks below A7, futhrer adjustment would be needed.

Confused by, if there are blanks below A7. The header is on the A7, the data starts on A8 and may have blanks though out the length of the column. Does this not say start from A7 and get everything with data?

=OFFSET('Test Case Matrix'!$A$7,0,0,COUNTA('Test Case Matrix'!$A:$A),1

 

[jmiaebrown]

If Mike's formula does not fix it for you can you elaborate on the data type in A -- is it numeric or text ?

Thanks.
It is text data.
Since rows can be added or deleted I figured that I needed a dynamic range. I had this before trying to hard code the column limits. Each time I added and deleted rows this changed.

=COUNTIF('Test Case Matrix'!$A$8:$A$5000,$A10)

 

[sailepaty]

Define your range like this,

='Test Case Matrix'!$A$8:INDEX('Test Case Matrix'!$A:$A,MATCH(REPT("z",255),'Test Case Matrix'!$A:$A))

Regards

 

[jmiaebrown]

Define your range like this,

='Test Case Matrix'!$A$8:INDEX('Test Case Matrix'!$A:$A,MATCH(REPT("z",255),'Test Case Matrix'!$A:$A))

Regards

Thank you very much! This almost works. However, if there isn't anything in the cell then there is a error.

Let me clarify what I am doing. On SheetA I have some columns named Module/Function that starts on row A8 and a Pass/Fail column that starts on K8. On SheetB I want to total all of the testcases executed for this Module/Function. I evaluate the Pass/Fail column to determine if the testcase has been executed using an "And" condition

Here is the formula I originally used. This works great until I delete rows then the $A$8 could end up $A$48. When I go back and add data to the sheet anything above $A$48 isnt evaluated. So based on what I have been reading in my google search of VBA Excel, I thought I need a Dynamic Range to replace the column ranges for A and K.

=SUM(IF((('Test Case Matrix'!$A$8:$A$5000=$A9)*(IF(ISBLANK('Test Case Matrix'!$K$8:$K$5000),0,1))) > 0,1,0))

I have defined 2 dr's as you suggested and I am trying to put them in the formula. I think the ISBLANK is giving me problems because if there isn't any data I get a N/A ref error.

=SUM(IF(((TCMModuleFunctionStartData=$A9)*(IF(ISBLANK(TCMPassFailStartData),0,1))) > 0,1,0))

Thanks for your assistance.

 

[sailepaty]

Try,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
1.- Define name LastRow like this:<o></o>
=MATCH(REPT("z",255),'Test Case Matrix'!$A:$A)<o></o>
<o> </o>
2.- Define name TCMModuleFunctionStartData like this:<o></o>
='Test Case Matrix'!$A$8:INDEX('Test Case Matrix'!$A:$A,LastRow)<o></o>
<o></o>
3.- Define name TCMPassFailStartData like this:<o></o>
='Test Case Matrix'!$K$8:INDEX('Test Case Matrix'!$K:$K,LastRow)<o></o>
<o> </o>
Now your formula could be,<o></o>
<o></o>
=SUMPRODUCT((TCMModuleFunctionStartData= $A9)*(LEN(TCMPassFailStartData)>0)) <o></o>
<o> </o>
I hope it helps.<o></o>

 

[jmiaebrown]

Try,<o></o>
<o></o>
1.- Define name LastRow like this:<o></o>
=MATCH(REPT("z",255),'Test Case Matrix'!$A:$A)<o></o>
<o></o>
2.- Define name TCMModuleFunctionStartData like this:<o></o>
='Test Case Matrix'!$A$8:INDEX('Test Case Matrix'!$A:$A,LastRow)<o></o>
<o></o>
3.- Define name TCMPassFailStartData like this:<o></o>
='Test Case Matrix'!$K$8:INDEX('Test Case Matrix'!$K:$K,LastRow)<o></o>
<o></o>
Now your formula could be,<o></o>
<o></o>
=SUMPRODUCT((TCMModuleFunctionStartData= $A9)*(LEN(TCMPassFailStartData)>0)) <o></o>
<o></o>
I hope it helps.<o></o>

Shucks... I still doesnt work. Getting #N/As now....
I'll keep trying, maybe its something I am doing wrong.

Thank you.

 

[sailepaty]

Try this in a blank sheet and let me know where do you get the error:

A1 = LastRow
A2 = SUMPRODUCT((TCMModuleFunctionStartData=$A9)*1)
A3 = SUMPRODUCT((LEN(TCMPassFailStartData)>0)*1)

Thanks