Dynamic Named Ranges

C

clane

I am trying to build a report in excel that auto updates a query from an
access database and I would like to use named ranges to make the formula
writing easier. The problem is I dont know exactly how many rows are
going to be coming in from the query each time as there is potential
for the dataset to grow.

Now i have the ranges defined as $A:$A but I am wondering if there is
a better way to do this....

Thanks in advnace for your help

Chuck
 
D

Don Guillett

try defining a name and using the offset formula
=offset($a$2,0,0,counta($A:$a),1)
 
C

clane

I have another question about this formula

i went ahead and used it to define my named ranges on a sheet that is a
refreshable query from an access database. I however I made some
changes

for each name I used the countA function referencing the same
cells(column A) becasue some of the other columns have blank values
mixed in and i also made it countA -1 so that the reference stops at
the same point as the data.

My question is that I am trying to use the name references in an array
formulat that is summing on multiple criteria but it keeps returning
"0" I recreated the array formula without the named ranges to verify
my data was ok and it returned the correct value is there something I
am doing wrong using these names here is my formula

{=SUM((City_Code05=A7)*(Bk_Code05=D7)*Delv_Units05)}


Thanks!
 
Top