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 :-

--
Air_Cooled_Nu

-----------------------------------------------------------------------
Air_Cooled_Nut's Profile: http://www.excelforum.com/member.php...fo&userid=1573
View this thread: http://www.excelforum.com/showthread.php?threadid=27368