Dynamic Nammed Range

[Michael]

I have an Access database that sucks data out of excel workbooks. It
uses named ranges to populate the ddatabase. In a new excel workbook I
defined some dynamic named ranges (ranges definitoins that use
formulas) instead of just referencing cells. Such as =sum(a1:a5).
These dont get sucked out even though they have data in them.

Is there some reason the these ranges would not work as others do when
I am sucking data out? I noticed they dont show up in the name drop
down. Do they not exist the same way as simple named ranges do?

Thanks - any help would be appreciated.

Michael

 

[GS]

Michael used his keyboard to write :

I have an Access database that sucks data out of excel workbooks. It
uses named ranges to populate the ddatabase. In a new excel workbook I
defined some dynamic named ranges (ranges definitoins that use
formulas) instead of just referencing cells. Such as =sum(a1:a5).
These dont get sucked out even though they have data in them.

Is there some reason the these ranges would not work as others do when
I am sucking data out? I noticed they dont show up in the name drop
down. Do they not exist the same way as simple named ranges do?

Thanks - any help would be appreciated.

Michael

Dynamic ranges are defined in the DefinedName dialog and their RefersTo
formula should include the OFFSET function and COUNTA function. The
point of a dynamic range is so its address can change according to its
contents. The formula you posted here does not define a dynamic range!

 

[Michael]

Michael used his keyboard to write :





Dynamic ranges are defined in the DefinedName dialog and their RefersTo
formula should include the OFFSET function and COUNTA function. The
point of a dynamic range is so its address can change according to its
contents. The formula you posted here does not define a dynamic range!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Fair enough. I was not sure what to call it. It is a Name defined with
a function but I still have the same issue. If defined with a fuction
does it exist the same way as a direct definition? For some reason it
does not work on names I defined that way. Anybody have an idea why?

 

[GS]

Michael wrote :

Fair enough. I was not sure what to call it. It is a Name defined with
a function but I still have the same issue. If defined with a fuction
does it exist the same way as a direct definition? For some reason it
does not work on names I defined that way. Anybody have an idea why?

Dynamic ranges don't exist if there's nothing in them. For example, to
create a dynamic range in columnA under the heading 'Date' in cell A1
you would define it as:
Name: sheetname!EntryDate
RefersTo: =OFFSET($A$1, 1, COUNTA($A:$A) - 1, 1)

...so it starts in A2 and continues for however many values are entered
under the Date heading, and is 1 column wide. There's some rules you
have to adhere to, though, if you want things to work correctly:

1. If there's not at least 1 value in A2 then the range doesn't
exist.
2. There can't be any empty cells between the header and last row.

HTH

 

[GS]

Oops! that RefersTo should read:
=OFFSET($A$1, 1, 0, COUNTA($A:$A) - 1, 1)

 

[Michael]

Oops! that RefersTo should read:
  =OFFSET($A$1, 1, 0, COUNTA($A:$A) - 1, 1)

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thank you. Inserting zero in my empty cells did the trick.

 

[The_Giant_Rat_of_Sumatra]

Thank you. Inserting zero in my empty cells did the trick.

That's nice.

I have been simply defining a range by marking and naming it, and from
then on, as long as I only add rows INSIDE that range (ie insert), the
range automatically expands. It only fails if I attempt to add a row add
the end of the range. Same for columns, of course.

I guess it would depend on how often the data gets changed. My
examples were lookup tables and morphed little once created, yet were
expandable within the above limits.