Dynamic Range Names - 2 Qs

[ken smith]

XL2000 on NT4 - I want to refer to named ranges on another
workbook. Those names are dynamic. If the source wkbk is
closed when the other is opened, I get an error saying the
names do not refer to rectangular ranges. The dynamic
names are in either of 2 forms:
=OFFSET(Lists!$D$2,0,0,COUNTA(Lists!$D:$D)-1,1)
=INDEX(Lists!$D:$D,2):INDEX(Lists!$D:$D,COUNTA(Lists!
$D:$D))
1) Is this possible?
2) One of the reasons for the ranges is to populate lists
for data validation dropdowns. As this can't be done from
another wkbk, I have array-entered the range name
({=OtherWkbk!MYRANGE}) in more cells than the list will
ever have. Those cells beyond the length of the source
range return #N/A. For example if the range has 3 values
and I array-enter it in 6 rows I get (notwithstanding the
linking problem described above):
Value1
Value2
Value3
#N/A
#N/A
#N/A
I would like to name a range for the dropdowns that
contains all values up to and including the last one that
is not #N/A. Any ideas? Or perhaps a better way?

TIA,
Ken

 

[Arvi Laanemets]

Hi

1) It isn't possible! But you can mirror your table/list into some sheet in
your workbook through links, and then create your named range from there.
You can hide the sheet from user.
2) No need for this! Look above.

 

[ken smith]

Thanks,

Mirror how? Clearly my array-entering kludge wasn't too
smooth?

TIA,
Ken

 

[Arvi Laanemets]

Hi

P.e., open the source workbook, and into cell A1 on some empty sheet in
target workbook enter
=IF('[SourceWorkbook.xls]SourceSheet'!A1="","",'[SourceWorkbook.xls]SourceSh
eet'!A1)
Close source workbook.
Copy the formula on target sheet to range, which shows wanted data.

 

[Guest]

Yes I did that and it works well. I guess I was hoping for
a more elegant solution.

Thanks again

-----Original Message-----
Hi

P.e., open the source workbook, and into cell A1 on some empty sheet in
target workbook enter
=IF('[SourceWorkbook.xls]SourceSheet'! A1="","",'[SourceWorkbook.xls]SourceSh
eet'!A1)
Close source workbook.
Copy the formula on target sheet to range, which shows wanted data.


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets

Thanks,

Mirror how? Clearly my array-entering kludge wasn't too
smooth?

TIA,
Ken table/list
into some sheet in saying
the

.

 

[Dave Peterson]

How about this:

I created a workbook (book3.xls) with a dynamic name in it--

I put my data in A1:Ax and used TestBook3 as the name:
I used this:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))

Now I kept that workbook open and in a new workbook (book4),
I added a name: FromBook3 with this formula:
=book3.xls!testbook3

I put this formula in A1 of sheet1 and dragged down until I was done, then did
some more (until A300).

=frombook3
(I got a bunch of #value!'s back, too)

Then (still in book4), I created one more name:
NoErrors
with this formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)
-COUNTIF(Sheet1!$A$1:$A$300,"#value!"))
(all one line)

Then I used NoErrors as my list range for my data|validation cell.

It seemed to work ok.

====
Debra Dalgleish has some notes for this kind of thing at:
http://www.contextures.com/xlDataVal05.html
(but she doesn't deal with the dynamic part of it (yet!!).