Excel2000: Strange behaviour for named range

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.)


-- 
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)


0
garbage (651)
10/13/2004 1:49:57 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
364 Views

Similar Articles

[PageSpeed] 55

Reply:

Similar Artilces:

Summing numbers between a date range
I need to sum values in a row of data where the date in the column of the row falls within a certain date range. Here is an example of the data. The data to be summed is in sheet one and the dates used for the criteria are in sheet 2. Sheet_1: col a col b col c col d Nov 15/92 Feb 15/93 Sep 15/93 Feb 15/94 15.00 20.00 30.00 10.00 Sheet_2: col a col b Jan 1/93 Dec 31/93 Jan 2/94 Dec 31/94 I basically want to sum the data in Sheet_1 that is between a date range based on Sheet_2. For example, sum values in sheet_...

Count File Names
Hi All, I having an issue with version control on Buisness cases. I currently have a spreadsheet that looks into a folder and picks up details from a number of files. However, they require version control numbers so there will eventually be multiple files say 2010-01 v1.0, 2010-01 v1.1. Is there a way I can: 1) Get a macro to count the number of 2010-01 files there are in the folder, and 2) Get my version control sheet to look at the latest version of the file (in this example: 2010-01 v1.1) Cheers, Chris See if this code works. I assume that 2010-01 is year and month. ...

M2004: Change name on home page?
I'm using Money 2004. On the home page it says "So-and-so's Money Home Page". How can I change the name that appears there? -Rod > I'm using Money 2004. On the home page it says "So-and-so's Money Home > Page". How can I change the name that appears there? Figures... I always seem to find a solution right after posting a question! Click Accounts & Bills, Account Setup, Update Personal Information. -Rod ...

Getting row indexes on Range
(I refer to C# code, but answers in VB are welcome) I have a Range in Excel, which includes several cells (the cells the user selected in the Excel sheet). The range might include the following cells A2, B7, G4. This means that the cells might not be connected. If I look at myRange.Cells.Count, it will return 3. If I look at myRange.Row, it will return 2 (if A2 was the first selected row by the user). Now, I need to get the row numbers of all selected rows, so in the above range, I need an int[] of {2, 7, 4}. But I can't see any solution to go through the Cells and get t...

Lookup from a range on separate sheets
Hi, I am trying to look up values from the same fixed range (c50:E200) in 45 sheets and extract the values onto one sheet (same workbook) 3 columns at a time (ie first sheet B-D, second sheet E-G etc). I can't work out how to put the sheet name (or a reference to the sheet number) in a formula in the destination sheet - is it possible or do I have to use a macro? I can write the code for a macro, but as it is statistical process control limits which are added to each month, it means rerunning the macro every time Any help much appreciated! Paul Paul, In the sheet/cells that ...

Name prefixing in reply message
All, I'm not even sure what terms to google for related to this problem. If you get an email from someone in the Office XP version of Outlook, and you click reply to that email, then start typing inside the portion of the message that contains the original message you replied to, it will automatically prefix everything you write with your name. How does one turn this off? Thanks in advance! TwistedPair wrote: > I'm not even sure what terms to google for related to this problem. If you > get an email from someone in the Office XP version of Outlook, > and you click...

Calendar Formatting Date Range problem
Hi, I am trying to make a monthly calendar that our administrator can update easily with new dates, and mail out. I've formatted the calendar and it looks great as it is right now for the month of April. However, when I choose "change date range" it reverts back to the original formatting and I have to start all over. I realize this is because I used a "design object". However, if I don't use a design object, then I can't change the date ranges. How do I change the date range on my calendar without losing all the formatting changes I made? Thanks T...

create chart from non adjacent range
I need to know how to do this Select one area, then hold CTRL while selecting the next area, etc. Then run the Chart Wizard as usual. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "rita" <rita@discussions.microsoft.com> wrote in message news:18C9672C-0EAB-401C-8798-4C51158EAF8B@microsoft.com... >I need to know how to do this ...

Comparing first and last names in two lists #2
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

>> Calendar Control drag to select range
Hi, using MS Access 2003, is it possible to allow a user to drag to select a range of dates and, if 'yes', how do you store the selected date range? Many thanks, Jonathan Not that I know of. Storing a range of dates is quite simple though. You store the start date and the end date and use a calculation to show all the dates between. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jonathan" <Jonathan@discussions.microsoft.com> wrote in message news:4F22299F-3765-40D2-AA03-67FB42FFC07A@microsoft.com... > ...

Different display names
Hi Is there any way to have an account display differently in the FROM field compared to the GAL (Exchange 2003). We would like the user to appear in the GAL as "Bob User" but when an external recipient reads an email it should be displayed as "Bob of Company <bob.user@company.com>". Cheers Stuart ...

File name from a cell
I am trying to have date in a cell become the file name and appear i the save as dialog box as the file is saved the first time -- Message posted from http://www.ExcelForum.com One way: Option Explicit Sub testme01() Dim mySuggestedFileName As String Dim myFileName As Variant With Worksheets("sheet1") mySuggestedFileName = "c:\my documents\excel\test\" _ & Format(.Range("a1").Value, "yyyy_mm_dd") & ".xls" End With myFileName = Application.GetSaveAsFilename _ ...

Can't get Date Range to work from Form to Report
Hi, I've followed Allen Browne's directions very closely for creating a form for a date range to limit results to that date range on a report and can not get it to work for some reason. I've read many threads on this board which all say the same thing and it seems to work for everyone else. :( Here's what I have: A form with two unbound text boxes called [txtDateBegin] and [txtDateEnd]. The form is called "frmDateRange" and the report is called "Sales Activity Report". The two unbound text boxes are set to "Short Date" format which the user ...

Unlocking named ranges
I am working with a spreadsheet created by a former employee. He used named ranges a lot. I am trying to understand the worksheet. When I see an equation in a cell like =Sum(Shipments) I feel I know what it means. But how do I learn what the actual cell reference is ie = Sum(D2:G2).? TIA Insert a new, empty worksheet into the workbook. Then you can use The Insert-> Name-> Paste menu option and click on the Paste List button to get a listing of all the names and the definitions "JohnL" wrote: > I am working with a spreadsheet created by a former employee. He used...

Adding company name to the "From" field
Hopefully this is an easy problem. We are running Exchange 2003 on Windows Server 2003 in a mixed domain. (Win2k and Win2003 servers) Lets say I am working for a company named "XYZ". The owner of the company wants the "from" line in the email client to say "Bill Smith, XYZ Company". Unfortunately I can not find a way to do this. Any help is appreciated. Bob Hi Bob, Just use ADModify to rewrite the Display name for every user in your company, or for users of that company (if companys are sharing and AD) ADModify can be found here: http://www.gotdotnet.co...

Interesting behaviour of GetMenuString() across platforms
Hi, I am using "CMenu::GetMenuString()" to retrieve the String for the MenuItem() in Visual C++ 6.0, somewhat like: ~~~ Code Snippets ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ pPopup->GetMenuString(ID_BLOCK_WATCHER_CHAT,strText,MF_BYCOMMAND); ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Interesting thing is, the above snippet works fine across all the platforms but Windows NT. Would like to make mention of that, we are developing a Unicode application (integrated with MSLU - Microsoft Layer for Unicode). Kindly suggest what might have been going wrong...

edit suggest names when completing To, cc and bcc fields
Apparently I don't have an Address Book linked to Outlook 2000. No matter, because I seem to be ok with the suggest names feature, which I guess is not Address Book-linked, but linked to a memory bank of names and corresponding addresses typed in during the past. However, here is my question: How do you edit this Suggest Names list? In particular, A. Suppose you want to stop Outlook from suggesting a particular name because it is not current anymore. Worse, you might inadvertantly click on it and send msg to somebody you don't want to receive it. Is there a way to delet...

reading file name from folder function
I am using Access 97. Just wonder if there is a fuction which can read all the file name from a folder and put it to a column in a table. Please let me know. Thanks. Hi. >I am using Access 97. Just wonder if there is a fuction which can read all >the file name from a folder and put it to a column in a table. Please see the getFileInfo( ) function in the tip, "How to compare the contents of two directories," on the following Web page for an example: http://www.access.qbuilt.com/html/vba1.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http...

Domain name not always needed to log into OWA?
This is something I've wondered about for a while. Why is it that only some of our new Exchange Server installs insist on the OWA login <up to 13 chars. of domain name>\<user name> while others have no trouble with only <user name>? "Julie" <julie@somewhere.org> wrote: >This is something I've wondered about for a while. > >Why is it that only some of our new Exchange Server installs insist on >the OWA login <up to 13 chars. of domain name>\<user name> while >others have no trouble with only <user name>? Probably b...

Named Ranges
Hi! I created several worksheets that contained huge lists, so I created a dynamic named range. Now that the name has been inserted in my array formulas, my Macro runs very slowly. The Macro unprotects one of my sheets, sorts the data in a table, hides the blank rows and the protects the sheet again. There are no related named range formulas in that worksheet, so I'm not sure why it would be affected. The Macro ran quickly when I had a defined range of data. Would giving a named range like 'Date' and having text in my workbook with 'Date' create conflicts? Could this...

Report names in listbox
I recently upgraded to Access 2007 and am having some trouble with the changes. I have a form with a listbox in which I would like to display the names of all the reports in the database. I've tried using the following as the rowsource for the listbox, but in form view nothing is displayed. Does anyone know how to do this? TIA, Ken Warthen kenwarthen@gmail.com "SELECT MSysObjects.Name FROM MSysObjects WHERE (((Left([Name],1))<"~") AND ((MSysObjects.Type)=-32764)) ORDER BY MSysObjects.Name;" I think you made a slight typo. Try (((Left([Name],1))<>&quo...

Send Emails With A Different Name Than How Its Listed in the GAL
Hi all, I have a quick question: My client is unhappy with how his emails get sent out from MS Outlook. The GAL on our server lists names as [Last Name], [First Name]. This is how the majority of our clients want it. However, he wants his to be [First Name] [Last Name]. So, all recipients getting emails from him will see his name as First Name, Last Name. We can't do this for him on our GAL, since it would obviously cause an inconsistency if we did it only for one name. But, I figured I'd post here to see if anything else can be done to accomodate him. Thanks in advance for your help...

Switching Names Around
Hey gurus- I am getting a list of names imported into Excel. All the names are in the order of LAST NAME FIRST MI/[SPOUSE] FIRST [SPOUSE] MI. All of them have their last name at the beginning, and they're all merged into the same cell. I am trying to print out mailers, so I need the 1st name moved to the back of the line so it prints out normally. How would I write a macro to do this? The only way I can think of is to signal is off the very 1st SPACE- everything before that space gets added to the end of the line. Is this possible to do? How do I do this? I'm not very good at VB, so...

Access server name?
Hi Experts: I have been using SQL Server 2005 and I'm now trying to move to Access (Microsoft Office Access 2007). One question, with SQL Server 2005, there is a Windows Service called "SQL Server" which is running all the time and ready to be connected from a database application. In the case of Access, what is the equivalent program? I do not see any Access program in the Windows Service List. Thanks in advance! Polaris In news:OC1gc%23kpHHA.4872@TK2MSFTNGP03.phx.gbl, Polaris <etpolaris@hotmail.com> wrote: > Hi Experts: > > I have been using SQ...

Using a named range as a data source for a chart
Hi, I'm trying to build a chart that that has a named range as its data source. The named range refers to a table that is 2x3 and is called geographydata. Is there any way that I can have the source data for the graph to be geographydata? Thanks! Michael You can enter the name, preceded by the worksheet name and exclamation point, for the data range, but Excel will convert this name to its cell address. If you want the chart to be dynamic as the name changes its size, you have to define a name for each series' data. This will not dynamically change the number of series in the ch...