Dynamic Nammed Range

  • Thread starter Thread starter Michael
  • Start date Start date
M

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
 
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
Click to expand...

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 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
Click to expand...

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?
 
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?
Click to expand...

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
 
Thank you. Inserting zero in my empty cells did the trick.
Click to expand...


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.
 
Back
Top