Dynamically filling a static name (for querying an Excel range)

[Air_Cooled_Nut]

Named ranges are very useful, especially for defining a data source tha
can change. I found a simple solution that I hope others will fin
useful if they use named ranges a lot and need an alternative way t
feed the named range data to external destinations. MVP's please fee
free to further explain/add/correct anything and add to your web sit
(Dick's Clicks...hint hint!

Here's my situation:
Customer is using Excel as a database. I know, I know, not smart and
explained why they should move it to Access or other real db tool.
Anyway, I have a form that updates their Edb (Excel db). The data i
this Edb is used by another workbook for some pivot table reports. Th
rows represent a sales opportunity and the columns includ
customer/account data AND monthly/quarterly/yearly forecasting dollars
Yeah, not pretty! The problem is, the data can grow (more rows adde
AND more columns added). Well, 'capturing' this slowly growing Edb i
easy using a named dynamic range:
INSERT / NAME / DEFINE...
our standard name for this is "dynamic"
=OFFSET('sheetname'!$A$1,0,0,COUNTA('sheetname'!$A:$A),COUNTA('sheetname'!$1:$1))

No problem, works fine. EXCEPT you can't use a dynamicly named rang
as input for importing external data into a pivot table. When yo
eventually get to the "Select Table" dialog box you will not see th
named dynamic range as a choice. What to do? Well, I don't know how
did this but it came to me and it worked the first time I tried it (th
"A4" is where my particular range started, yours may vary):

Range("A4").Select 'Set the 'home' cell to where the range start
(upper left corner of range)
Sheets("sheetname").Range(Range(Selection, Selection.End(xlToRight))
Selection.End(xlDown)).Name = "ReportingData" 'Select everything t
the right and down & give the range a name

BAM! Here you get a dynamically created range that has a Name you ca
use for feeding external reports -- well, it can be used for interna
reports as well but that's not so challenging :-

 

[keepITcool]

ehmm... what's DYNAMIC about this?

you're just defining a name which points to a STATIC area.
(except that when you define it it takes the size
of first row/first column into account.

without somebody (do you have an eventhandler?)
running that code.. the range will NOT be updated
when the user adds data ..

(and wasn't that the idea of a "Dynamic" named formula?

More important:
... your snippet contains a fatal flaw

it sets a range on the worksheet 'sheetname'
however it's dimensions are decided by the selection
on the ACTIVE sheet...ouch!






keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Dick Kusleika]

ACN

I like it. Two points and two questions.

Don't use the Select method unless you have a good reason. This isn't one.

With Sheet1.Range("a4")
.Resize(Application.CountA(.EntireColumn), _
Application.CountA(.Parent.Cells(1,1).EntireRow)).Name =
"ReportData"
End With

Some of your Range references are unqualified. For instance, if you were on
a sheet other than "sheetname" it would cause problems.

Questions 1: Could there ever be a case where the Edb was not rectangular?
For instance, if you had 10 rows in your data base (A4:A13) and then you
decided to add another column, but didn't want to go back and the change the
existing rows. You know what, forget that. You CountA on row one because
it has your headers and they will reflect the correct number of columns
regardless of missing data in any of the records. Do I have that right?

Question 2: Where's the best place to put this code? You want to make sure
that name points to the right range, but you don't want to run this code too
often or it will slow you down. If you know that this name will only be
used for external data, then I guess the BeforeClose event would be as good
as any. I'm basing that on the assumption that you don't query open
workbooks. What do you think?

Overall, I like this solution. Look for a blog post on www.dicks-blog.com
soon.

 

[Stephen Bullen]

Hi Air_Cooled_Nut,

Well, 'capturing' this slowly growing Edb is
easy using a named dynamic range:
INSERT / NAME / DEFINE...
our standard name for this is "dynamic"
=OFFSET('sheetname'!$A$1,0,0,COUNTA('sheetname'!$A:$A),COUNTA('sheetname'!$1:$1))

No problem, works fine. EXCEPT you can't use a dynamicly named range
as input for importing external data into a pivot table. When you
eventually get to the "Select Table" dialog box you will not see the
named dynamic range as a choice. What to do?

You could always just type in the name when asked for the source range:

Book1.xls!dynamic

Works for me! And within the workbook, you can just type the name dynamic, or press
F3 to see it listed.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk