named ranges in other workbooks

i have a range of cells whose values are validated from a list determined by
a name

the name is determined by a formula which references a range of cells in a
different workbook (i'm on a office network...)

the problem is:
    if the other workbook is open (on my desktop?) the name is o.k. and the
validation is fine...
but:
    if the other workbook is not open, the name results in an error

i didn't think that this is the way it's suppose to work...
AURGHH


0
MK5968 (6)
10/8/2003 6:58:59 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
349 Views

Similar Articles

[PageSpeed] 6

Mark,
Do you use a full reference such as ='C:\Documents and Settings\Owner\My
Documents\Book1.xls'!profit
Bernard

"Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
news:eKKmE4cjDHA.1284@TK2MSFTNGP09.phx.gbl...
> i have a range of cells whose values are validated from a list determined
by
> a name
>
> the name is determined by a formula which references a range of cells in a
> different workbook (i'm on a office network...)
>
> the problem is:
>     if the other workbook is open (on my desktop?) the name is o.k. and
the
> validation is fine...
> but:
>     if the other workbook is not open, the name results in an error
>
> i didn't think that this is the way it's suppose to work...
> AURGHH
>
>


0
bliengme (657)
10/8/2003 7:59:08 PM
yeah, i do...

=OFFSET('E:\@ Timesheets\[Job Nos for Timesheets.xls]Project
List'!$F$2,0,0,COUNTA('E:\@ Timesheets\[Job Nos for Timesheets.xls]Project
List'!$F$2:$F$130),1)



"Bernard V Liengme" <bliengme@stfx.ca> wrote in message
news:eD8uiadjDHA.1740@TK2MSFTNGP12.phx.gbl...
> Mark,
> Do you use a full reference such as ='C:\Documents and Settings\Owner\My
> Documents\Book1.xls'!profit
> Bernard
>
> "Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
> news:eKKmE4cjDHA.1284@TK2MSFTNGP09.phx.gbl...
> > i have a range of cells whose values are validated from a list
determined
> by
> > a name
> >
> > the name is determined by a formula which references a range of cells in
a
> > different workbook (i'm on a office network...)
> >
> > the problem is:
> >     if the other workbook is open (on my desktop?) the name is o.k. and
> the
> > validation is fine...
> > but:
> >     if the other workbook is not open, the name results in an error
> >
> > i didn't think that this is the way it's suppose to work...
> > AURGHH
> >
> >
>
>


0
MK5968 (6)
10/8/2003 8:23:52 PM
My guess is that OFFSET is one of those functions that need all files open.
Alternatively, experiment with a file that has no spaces in its name.
I try to avoid spaces in names because I have had network difficulties with
them.
Bernard

"Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
news:%23ASehndjDHA.964@TK2MSFTNGP10.phx.gbl...
> yeah, i do...
>
> =OFFSET('E:\@ Timesheets\[Job Nos for Timesheets.xls]Project
> List'!$F$2,0,0,COUNTA('E:\@ Timesheets\[Job Nos for Timesheets.xls]Project
> List'!$F$2:$F$130),1)
>
>
>
> "Bernard V Liengme" <bliengme@stfx.ca> wrote in message
> news:eD8uiadjDHA.1740@TK2MSFTNGP12.phx.gbl...
> > Mark,
> > Do you use a full reference such as ='C:\Documents and Settings\Owner\My
> > Documents\Book1.xls'!profit
> > Bernard
> >
> > "Mark Kubicki" <MK@KuglerTillotson.com> wrote in message
> > news:eKKmE4cjDHA.1284@TK2MSFTNGP09.phx.gbl...
> > > i have a range of cells whose values are validated from a list
> determined
> > by
> > > a name
> > >
> > > the name is determined by a formula which references a range of cells
in
> a
> > > different workbook (i'm on a office network...)
> > >
> > > the problem is:
> > >     if the other workbook is open (on my desktop?) the name is o.k.
and
> > the
> > > validation is fine...
> > > but:
> > >     if the other workbook is not open, the name results in an error
> > >
> > > i didn't think that this is the way it's suppose to work...
> > > AURGHH
> > >
> > >
> >
> >
>
>


0
bliengme (657)
10/8/2003 9:16:49 PM
Reply:

Similar Artilces:

cell outside range
Intermittently, a cell erroneously appears outside the range of cells in an active worksheet. This cell contains duplicate data from one of the other cells. I am not able to replicate the sequence of events that causes this cell outside the range of cells to magically appear. I wonder if anyone else has seen this problem. Thanks, Mrs. T Are there any macros in any open workbook? "Mrs. T." wrote: > Intermittently, a cell erroneously appears outside the range of cells in an > active worksheet. This cell contains duplicate data from one of the other > cells. I am ...

Placing a Date Range on a Report
I have several reports that have been created in Microsoft Access that require a date range in order to pull the requested data. The date range was set up in the query that was built to run the report. Is there a way to get that date range displayed on the report? ...

lable ranges in Excell
I am working with a customer list that I composed in excell to allow me to set up mailers & keep track of their address,dob etc I need it in out look so I can sink my new ipack so I dont have to enter all this data buy hand. In out look it says that you can import the data but it will not as it comes up with " the microsoft file ....................................... has nonamed ranges of tha data you want to import". I found where to name the rnages ( next to the address bar in excell but I am not sure of what to do. The office assent is pointing me to make them a "...

Receivables Statement Ranges
How can I get the User Defined Field 2 into the drop down box for Ranges on the Print Receivables Statements page? I see the customize User Defined Field 1 in there but not the second after I have customized it and added data into the field. Is there something I am missing? Thanks! No, the first field is automatically provided in the search but not the second. This would require a modification by a programmer -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublicat...

Cannot assign to range object
Hi, I'm trying to use Visual Basic to program a macro that will alter a worksheet (Excel 2000, version 9.0.2720; Windows 2000.) But every time the code gets to a place (any place) where I make use of a range object, the code stops running and a small popup displays the message, "Visual Basic \newline 400". Nothing else! Just "400". I suspect that, since I'm new to VB, I'm not setting up the objects correctly. Below is sample code (see *** for where it is breaking down): Private Sub addNewList(ByVal numEmpl As Integer) Dim mySheetName As Strin...

Extracting the data according the number of cell (at specific range) #2
I have a minor Excel formula problem. In my example has following data, cell A5 shows "apple", A6 shows "salt", A7 shows "sugar" and A8 shows "fish". On another hand, cell B3 is the "criteria" data showing 3. My question is to form an Excel formula which can extract the data from cell "A1" and then extract the 3rd data, which is "sugar" (that is the result I want). Althought the formula is counting from A1, but the excel formula know the data after "apple" and "salt" is "sugar" (cell fro...

Duplicate customized page for calendar, change date range goes to.
I publish a calendar for scheduling IT projects on my website and it's customized to have the look I want. Whenever I duplicate it for consistency and change the date range, the calendar goes back to othe default look. Is there a way to change the date range and keep the customized look of the calendar? ...

Still need help
The subroutine did not run as I received the Macro Disabled message. I clicked OK, and next time I opened the workbook, I didn't get the Macro Warning, but the subroutine apparently didn't run as the chosen cells were not blank. I don't want people who use my workbook to have to deal with macro warnings. Is this something that must be set on each user's computer, or can I set it in my workbook so the subroutine automatically runs on everyone's computer? Do I need to get a Certificate? Here's my code. Are the () and the " " necessary? I'm...

SUMIF using two columns in both Range and Criteria
I have a speadsheet with columns as under: Account Code Department Amount ------------ ---------- ------ 7000 12 �100 7050 5 �250 There are a couple of hundred rows with different account numbers and a variety of Department numbers. Is there a way I can use SUMIF to summarize the amounts by Code AND Department? In other words I want to sum ALL the amounts with Code 7000 and Dept 12 etc. Thanks! =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20) -- HTH Bob Phillips "Gordon" <go...

outlook 2002 is suggesting incorrect e-mail names in "to" field.
At one time, I sent an e-mail to an incorrect e-mail address for a friend of mine. Now, whenever I enter her name in the "to" field, outlook is suggests the incorrect address as well as the correct one. The incorrect address is not stored in my contacts or my outlook address book. Any suggestions (other than turning off "suggest names")? Thank you. When it comes up, right click on it and then hit the delete key. That should remove it. "Robyn B" <anonymous@discussions.microsoft.com> wrote in message news:083801c3a361$db1668e0$a401280a@phx.gbl... ...

How do I copy a worksheet from a workbook as csv file
I have a workbook with several worksheets. I want to record a macro that will save one tab, "CSV Data" as its own csv file to another location. Any ideas? Try this one to save the file in C:\ Sub Sheet_CSV_File() Dim wb As Workbook Dim strdate As String Dim Fname As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Fname = "C:\Part of " & ThisWorkbook.Name _ & " " & strdate & ".csv" Application.ScreenUpdating = False Sheets("CSV Data").Copy Set wb = ActiveWorkbook W...

#Name? error when opening a file
I'm having a problem opening older files that I used to have no problem with. I have an excel file with links to other workbooks (which I don't have). At first, I could open the file, say no to updating, and continue working. After several months, however, when I open the file, I can see the last #s updated when the prompt to update links appears. As soon as I say no to the update, the links all change to #Name? errors. What is causing the problem? ...

check names from contact list
When I want to send emails to a recipient is not in my contacts list, check names comes up. I cant find where to undo this request. ...

Change "Find Names" dialog in OWA
Hi there, Hope I have the right group.... I have a requirement to remove the "alias" details from the OWA "Find Names" dialog box .... My client doesn't want their users to see alias as it may confuse them. I have successfully removed it from the details template for normal Outlook clients, but can I remove it from the OWA dialog? Many thanks "Stuart Parker" <mrgimper@nospam-hotmail.com> wrote in message news:eCt9BMsjGHA.4212@TK2MSFTNGP03.phx.gbl... > Hi there, > > Hope I have the right group.... > > I have a requirement to rem...

Paste Label into Workbook
I want to print information I extracted/copied/pasted into a Word 2003 document. I want to print it on an address label of 2 labels per A4 page seperated in middle horizontally. I can then peel it off and paste it into my A5 paper workbook I write notes in. To do so I need to print on one of the 2 labels and preferably vertically to look like a A5 page. Hope this make sense. Although you can certainly find a "label" definition that will give you two labels (portrait) per landscape page, much the easiest approach is to use the "2 pages per sheet" setting under ...

How to change the name of a project?
How can I change a project's name? Is it enough to rename the .dsw, .dsp, .ncb, .opt, and .plg? Or is there something subtle that will make this not work? What about just the .dsw and .dsp and deleting the other files and recompiling? open u r dsw in any text editor and replace the name of the dsw with u r desired name. then rename the file as well. be careful that the find replace dowsnt change the name of the app class cpp n h and other files which too if created via AppWizard has the same name as the project. SY "Yasoo" <Yasoo@discussions.microsoft.com> wrote in m...

Newly married user needs last name changed.
Can email for her old name be forwarded or how should I handle this? I am using Exchange 2003 on Standard Server 2003. Just change the display name and then add a new address to the email addresses tab and hit the set default button. -- Thanks, Brian Desmond Windows Server MVP - Directory Services www.briandesmond.com "Throck" <Throck@discussions.microsoft.com> wrote in message news:1F4DB3AF-85D3-4FA7-B8B4-990ED26A58F8@microsoft.com... > Can email for her old name be forwarded or how should I handle this? I am > using Exchange 2003 on Standard Server 2003. ...

Chosing a Domain Name for .local
I have sort of decided to call our install "office.local". We will be changing our external Domain Name soon although it is not decided yet to what. This generic approach should avoid a reinstall to correct it later. Is it the MX record that has to change the current email address username@ourExternalDNS.org to this form or is it just the fixed IP which is configured? Is the A record adjusted in any way? -- happyhacker ------------------------------------------------------------------------ happyhacker's Profile: http://forums.techarena.in/members/188993.htm...

Length of Variable names
I must make changes to code produced by developers long gone. Is it unusual to create variable names 50 or 60 characters in length?!? > Is it unusual to create variable names 50 or 60 characters in length?!? It does seem long but its valid. As a practice I seldom use names this long. If most or all variable names are this long, I would say its unusual. --- Ajay I think this is perfectly legal, but it makes it difficult to type the names :o) I've never had a problem describing my variable's function in less than 10 characters, but I've seen plenty of programs where peo...

blocking display names in Outlook
I am receiving emails where the display name contains the word VIAGRA. The properties of the email are such that it appears that I am sending the emails to myself. Any ideas on how to setup a rule that will block these emails. I have tried a number of different rules none of which search the display name an the header only shows that I am sending the messgae to myself. I am using Outlook 2007 and Vista Ultimate. Thanks Sam The best fix is to set outlook's junk mail setting to high (Tools, Options, Junk mail options). Otherwise, use a rule that looks for words in the ...

Returning a range from an inner function
I have a range of data, which has had the DOLLAR() function performed on it. I would like to get the total of this range using the SUM() function. doing SUM(F1:F4) will not work, so I need to convert the data into values that will work. The VALUE() function is perfect for converting the data into values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) does not work because VALUE() will not accept a range as a parameter (and also does not return a range). I need to perform the VALUE() function on each cell before submitting it to SUM(). There must be some way to do this, cons...

Viewing Worksheets withing One Workbook
My PC Excel (2002) allows me to have many worksheets within one workbook. I can see the tabs at the bottom of the workbook to access each sheet. Does it work the same in the Mac 2004 Excel? The MAC person I am sending my PC workbook to says he can only see one worksheet when there should be four. He doesn't see any tabs at the bottom showing the different sheet names. What can I have him look for to find these sheets? Thanks once again :) No Tabs can be seen? In Excel for Windows you would go to Tools>Options>View> and checkmark "Sheet Tabs" or hit CTRL + P...

Date Range in 2007
Hi All I am currently upgrading a DB from Access 2000 to 2007. I have been using a Form "Report Date Range" which has the following : Private Sub Report_Open(Cancel As Integer) DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Payments Due" If Not IsLoaded("Report Date Range") Then Cancel = True End If End Sub Private Sub Report_Close() DoCmd.Close acForm, "Report Date Range" End Sub Private Sub Report_NoData(Cancel As Integer) MsgBox "There is no data for this report. Canceling report..." Cancel = ...

VNC on LAN by name?
I need to do a VNC connection to a computer on the LAN. It seems that VNC progs need an IP address. Is there any way to do the connection using the name of the computer? -- Dirk http://www.transcendence.me.uk/ - Transcendence UK http://www.theconsensus.org/ - A UK political party http://www.blogtalkradio.com/onetribe - Occult Talk Show In news:7v2h84FsfgU1@mid.individual.net, Dirk Bruere at NeoPax <dirk.bruere@gmail.com> typed: > I need to do a VNC connection to a computer on the LAN. > It seems that VNC progs need an IP address. > Is there any way to do the ...

Email recepients name ends up on CC field
I have a user that has an issue when replying to a message that causes the address to show up in the CC field. We use Exchange 2000 SP4 and Office XP SP3. Has any body seen this behavior before... Thanks Rich Tibbets ...