Another Dynamic Range problem

jmiaebrown

Board Regular
Joined
Jul 13, 2008
Messages
129
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​
52Module1 1 Do Test Case1 1 Do Step1
53 2 Do Step2
54 3 Do Step3
55Module1 2 Do Test Case2 1 Do Step1
56 2 Do Step2
57 3 Do Step3
58Module2 1 Do Step1
59 3 Do Test Case3
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Board Regular
Joined
Jul 13, 2008
Messages
129
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

Board Regular
Joined
Jul 13, 2008
Messages
129

ADVERTISEMENT

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

Active Member
Joined
Nov 1, 2005
Messages
279
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

Board Regular
Joined
Jul 13, 2008
Messages
129

ADVERTISEMENT

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

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

jmiaebrown

Board Regular
Joined
Jul 13, 2008
Messages
129
Try,<o:p></o:p>
<o:p></o:p>
1.- Define name LastRow like this:<o:p></o:p>
=MATCH(REPT("z",255),'Test Case Matrix'!$A:$A)<o:p></o:p>
<o:p></o:p>
2.- Define name TCMModuleFunctionStartData like this:<o:p></o:p>
='Test Case Matrix'!$A$8:INDEX('Test Case Matrix'!$A:$A,LastRow)<o:p></o:p>
<o:p></o:p>
3.- Define name TCMPassFailStartData like this:<o:p></o:p>
='Test Case Matrix'!$K$8:INDEX('Test Case Matrix'!$K:$K,LastRow)<o:p></o:p>
<o:p></o:p>
Now your formula could be,<o:p></o:p>
<o:p></o:p>
=SUMPRODUCT((TCMModuleFunctionStartData= $A9)*(LEN(TCMPassFailStartData)>0)) <o:p></o:p>
<o:p></o:p>
I hope it helps.<o:p></o:p>
Shucks... I still doesnt work. Getting #N/As now....
I'll keep trying, maybe its something I am doing wrong.

Thank you.
 

sailepaty

Active Member
Joined
Nov 1, 2005
Messages
279
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top