Q: Referencing named cells in external worksheet ?

Using Excel 2002.

I have a workbook with 12 worksheets (one for every month of the
year), wherein a lot of the information is looked-up (using VLOOKUP)
in simples arrays.

I saw no point in implementing the arrays as a 13th worksheet, because
I will have a yearly version of my monthly worksheets in one workbook
(so one for 2003, 2004, etc). If I change the array(s), I want them to
be reflected in all referencing cells.

Problem:

If my workbook containing my arrays (called "Global") is loaded, I
have no problem and the references to it read as:

(blabla) 'Global.xls'!Roster (blabla) where "Roster" is a named cell
in this example.

If "Global" is not loaded (and I don't why it should be), then the
references change to:

(blabla) 'F:\Excel\Global.xls'!Roster (blabla) and all cells
containing such references indicate "#REF!".

Why?
Any help appreciated.

Thanks.
M.T.
0
9/6/2003 11:25:02 PM
excel 39879 articles. 2 followers. Follow

0 Replies
624 Views

Similar Articles

[PageSpeed] 51

Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Renaming Partial File Names
Is it possible to rename part of a file name? (almost like a find and replace) My database pulls in the excel file names from a directory, with it's subfolders and contents as well. Every file begins with "Kay Form". Kay no longer works in our department, and I'd like to run a loop that replaces "Kay Form" with "Featured Track". I don't know how to do a partial replace though. Thank you in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 Checkout the Replace function. I...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Shortcut for inserting names?
Hi, I am building many formulas, each with many named cells as part of it. Currently I have to go to Insert | Name | Paste with the mouse each time I want to insert a name. I looked in Walkenbach's Excel Bible but couldn't find any mention of a keyboard shortcut or other shortcut. Is there one I could take advantage of? I've got Excel 2002. Thanks, Jamie ...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Indirect Method for Name Box Variable? #2
DARN The reason I want to do this is that I have to collect data on a dail basis but I have to display it on a graph on a weekly basis. I select the entire week of cells and give it a name like week52. The following week I have to select the entire week and name i week53. Everything is set up that I can just copy and the numbers will progres for whatever length of time I need to graph (months, years, etc) excep this. Maybe I'll just go back to using the range of cell numbers. Thanks guys -- Chris Brenna ----------------------------------------------------------------------- Chris Bre...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Sorting by file extension in a worksheet
Hello, I have an excel worksheet which has a list of file names from a directory and various stats about each file. I need to be able to sort them by the file extension. Eg. c:\documents\folderA\picture1.eps c:\documents\folderB\document.doc I would like to sort them by the ".eps" extension. I have tried using the Data,Sort menu and something like *?*.eps as the criteria. But am obvisouly missing something. Any help would be most appreciated. Thanks Karl You could use a helper column to extract just the extension then include this in your sort range and sort on the helper col...

If a worksheet name is = to test then a msgbox appears
I'm looking for a macro that will display a msgbox if a worksheet is = to test. For example, if the name of a sheet in a workbook is equal to test then display msgbox saying sheet already exists. Thanks Vick dim ws as worksheet set ws = nothing on error resume next set ws = worksheets("test") on error goto 0 if ws is nothing then msgbox "doesn't exist" else msgbox "already exists" end if Vick wrote: > > I'm looking for a macro that will display a msgbox if a worksheet is = to test. > > For example, if the name of a sheet in a w...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

Updating External Links Excel 2000 vs 2002/2003
I have a situation where I'm using Excel 2000 with a workbook containing references to another workbook. When opening the first workbook & the second workbook is not available, you can say "no" to the update external links, and still see all values as they were when the first workbook was last closed. However, when the same workbook is opened in Excel 2002 or 2003, the external links specified only as a cell reference show the proper data (e.g, =wbkname!E1), but when they are Excel formulae (specifically a SUMIF), I'm getting a #VALUE! error in the pertinent cells Is th...

Multipule Worksheets
How do you set Excel 2002 to open all spreadsheets into one document instead of having multipule documents open up across your toolbar? ...

changing a cell of 60 files
Hi all I've 60 files and another one which summarizes all of them.. I've to put a day in the cell a1 and then I'd like to cut and paste that day in the cell a1 of the other 60 files without having to change all of them manually. I'd like to save and then exit every single file.The files are named 0001 0002 0003..and so on. I know that this is possible with a macro..but I've got a problem.. It's possible not to have the prompt which asks for updates of the file everytime I open one of them?? Thanks for the help Rossella Hi Rossella http://www.rondebruin.nl/copy4.htm ...

changing the application name
hi, i have developed a project and after completion of that project there is need to change that applicatin name. I will explained clearly i have created a document./view project , which named has "aaaa" afte completion the project, by running that application, there is a frame which has title "aaaa" Now there is need to change that title to "bbbbb" how can we do that? i am using VC++.net plz let me know how to do that by, koti "Koti" <koti@nannacomputers.com> wrote in message news:OqHUbvNQFHA.1236@TK2MSFTNGP14.phx.gbl... >...

Connectning Outlook to MSN mail account (server names)
I need help trying to connect my outlook to my free MSN mail account. Please help, I need the server names to figure this out or a step by step guide to set this up. I have Microsoft Outlook 2000 Hi Steve please read this here http://home.arcor.de/andreas.roeder-privat/English%20Help.htm#19 -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzministerium.de "Steve" <aefwolf@msn.com> schrieb im Newsbeitrag news:0c9d01c46e8c$73b6c090$a501280a@phx.gbl... > I need help trying to connect my outlook ...

Name Badges with Graphics
I am trying to create a mail merge in Publisher 2003 that will contain the same graphic for each badge. My data source is a word table. I don't understand how to connect the graphic to the mail merge. Thanks in advance for your help. Help here: http://office.microsoft.com/en-us/publisher/CH062524751033.aspx Do you have Excel or Access? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "L. Roper" <LRoper@discussions.microsoft.com> wrote in message news:99ED9F25-AE38-4830-ABEA-23170D825AC9@microsoft.com... >...

how do i identically size cells to organize contents alphabeticall
how do i size the cells in excell to organize alphabetically The size of the cell has nothing to do with sorting them. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "entertainer" <entertainer@discussions.microsoft.com> wrote in message news:62754406-410E-4328-8A6A-679D001D7F70@microsoft.com... > how do i size the cells in excell to organize alphabetically As JoAnne points out, cell size has nothing to do with sorting. What is the current sate of your data and what would you like to see happen to i...

Data Validation circle cell position
Hi everyone. I want to create an alert that flashes a msgbox when a sheet is activated and contains data that is flagged as invalid by using the validation circles. In a large sheet, it is tricky to find all the cells that may contain invalid data which is why I want to be able to create a msgbox that lists the addresses or something like that. By looping through the shapes in the sheet, I can determine that these errors exist but I can't find out where they are as the data validation oval does not appear to have a .topleftcell location. I can find out where it is in ...