Dynamic Named Ranges
 

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


--
clane
------------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=475122

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

--
Don Guillett
SalesAid Software

"clane" wrote in
message ...

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


--
clane
------------------------------------------------------------------------
clane's Profile:
http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=475122

Thanks!!!!!!


--
clane
------------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=475122

glad to help

--
Don Guillett
SalesAid Software

"clane" wrote in
message ...

Thanks!!!!!!


--
clane
------------------------------------------------------------------------
clane's Profile:
http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=475122

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!


--
clane
------------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=475122

Sorry nevermind i figured it out


THanks Again


--
clane
------------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=475122