A
Arvi Laanemets
Hi
Some time ago I posted here a question about case, where a fixed named range
didn't get listed as table in ODBC query wizard, when the source workbook
was closed. And when I created a query with opened source workbook, then it
dind't refresh anymore, after the source workbook was closed. Now at last I
found out something, that maybe helps to find a solution, but I myself can't
explain it.
I discovered, that the range got listed, when I renamed it so that it's
index was less, i.e. the name was in top of names list in Define Name
dialog. Then I copied it under diffrent names, so that after every original
name in names list was a copy of my test range. I found out, that:
1) name index value didn't determine the visibility of name;
2) all fixed names in list before a specific dynamic named range
(PingidLst2) were listed, and no one after this named range was listed.
3) I renamed PingidLst2 to ZPingidLst2, and deleted old range - now ranges
until the range ZPingidLst2 were visible, and no one after that.
My conclusion: This strange behaviour was caused by dynamic named range
PingidLst2. For row2 on sheet Tootmine the range formula is:
=OFFSET(Normid!$B$1,MATCH(Tootmine!$C2,NormidArtikkel;0),,COUNTIF(Normid!$A:
$A,Tootmine!$C2);1)
About this named range:
On sheet Normid are stored time normatives for producing details (articles)
on various machines. The table is ordered by article and machine. The named
range returns the range from Normid.Machines column, where are listed all
machines which have normative for an article available in row on sheet
Tootmine, and is used as data validation list source on same row on sheet
Tootmine (list depends on article selected). (somewhat messy explanation,
but I did my best).
The dynamic range NormidArtikkel contains all articles in Article column on
sheet Normid
OK. I can rename the named range, and it's over. But I'm curious, how this
named range (which is ignored by ODBC wizard by definition) does interfere
with ODBC reading fixed names with greater index. Some clever ideas here?
(The only significant difference between PingidLst2 and other named ranges
is, that PingidLst2 refers to 2 different sheets.)
Some time ago I posted here a question about case, where a fixed named range
didn't get listed as table in ODBC query wizard, when the source workbook
was closed. And when I created a query with opened source workbook, then it
dind't refresh anymore, after the source workbook was closed. Now at last I
found out something, that maybe helps to find a solution, but I myself can't
explain it.
I discovered, that the range got listed, when I renamed it so that it's
index was less, i.e. the name was in top of names list in Define Name
dialog. Then I copied it under diffrent names, so that after every original
name in names list was a copy of my test range. I found out, that:
1) name index value didn't determine the visibility of name;
2) all fixed names in list before a specific dynamic named range
(PingidLst2) were listed, and no one after this named range was listed.
3) I renamed PingidLst2 to ZPingidLst2, and deleted old range - now ranges
until the range ZPingidLst2 were visible, and no one after that.
My conclusion: This strange behaviour was caused by dynamic named range
PingidLst2. For row2 on sheet Tootmine the range formula is:
=OFFSET(Normid!$B$1,MATCH(Tootmine!$C2,NormidArtikkel;0),,COUNTIF(Normid!$A:
$A,Tootmine!$C2);1)
About this named range:
On sheet Normid are stored time normatives for producing details (articles)
on various machines. The table is ordered by article and machine. The named
range returns the range from Normid.Machines column, where are listed all
machines which have normative for an article available in row on sheet
Tootmine, and is used as data validation list source on same row on sheet
Tootmine (list depends on article selected). (somewhat messy explanation,
but I did my best).
The dynamic range NormidArtikkel contains all articles in Article column on
sheet Normid
OK. I can rename the named range, and it's over. But I'm curious, how this
named range (which is ignored by ODBC wizard by definition) does interfere
with ODBC reading fixed names with greater index. Some clever ideas here?
(The only significant difference between PingidLst2 and other named ranges
is, that PingidLst2 refers to 2 different sheets.)