Excel 2002 Links 1

[janetlyn]

1. We have 7 computers hooked to a server.
2. All documents are saved to the server drive (M.
3. Have one Excel workbook (called Jobs) with a list of
numbers in the first column and names in the second
column.
4. Have second Excel workbook (called Timecard) with a
Vlookup, that when someone types in a number it go to
Jobs first column, and picks up the matching "name,
second column".
5. Both workbooks are located in the same folder.
6. Up until 6 months ago, we were using Windows NT.
Then, we leased 7 Dell computers. Now are using XP. and
Excel 2002.
7. A copy of the hard drive of each old computer was
made and placed in the c:\Documents and Settings for each
individual computer.
8. When people started opening the Timecard workbook, it
pointed at the Jobs folder located in the c:\D&S folder
instead of the server drive (M. No big deal, I just
updated the links to point to the M:\ drive.
9. When I saved and reopened the Timecard workbook, all
links were fine. Until the next day when they would open
the Timecard workbook again. (Each individual has their
own "Timecard" workbook, but there is only one "Jobs"
workbook.
10. I then went to each persons computer and changed
their options to always automatically save to the M:\
drive, instead of the c:\D&S folder. For some reason,
this worked on all the computers but one. Everyday, when
he opens the Timecard workbook, we have to go through and
redirect the links again.

Help please? Janet Lyn

 

[MMSantiago]

Ok. So the problem occurs when the other person updates the links on your file. The question that needs to be answered is: Which component is changing the links? It could be a Novell setting but I doubt it, because you didn't mention any Novell changes since switching machines. That would leave Excel and XP.

The important thing in any further tests is that you specifically log into BOTH the Novell server AND the XP workstation as the same person everytime. This goes back to an earlier reply, stating that both XP and Excel 2002 will remember separate configuration settings for EACH user who logs into the workstation.

For clarification, please verify:
* Every user has his/her own Novell login?
* When they log into the XP workstation, do they each have their own separate XP username and password or is there just a generic USER that everyone types in to get into the workstation itself?

Your clarification will determine how best to advise you on what to try next. In this instance, it's important to know exactly how the workstation is being accessed. Thanks.

 

[janetlyn]

When turning or unlocking a computer, each workstation comes up with a user-specific name and they have to type in a password they have chosen. If I delete their name and type in mine and my password (at their workstation), it will then give me another screen which makes me either sign on as ADMIN or put in the persons password that owns the workstation. I cannot just change the name to me and put in my password on the second password option. Hope that answers your question. Thanks for sticking it out with me. Janet Lyn

 

[MMSantiago]

That means that each user has their own login for Novell and every machine has two profiles (ADMIN and the user).

Try this:

* Get your file working again on your machine as you.
* Shut Down
* Start it up and log in as the user for novell and as YOU for the workstation
* Try the file.

If it asks for a link change and works, then it's the Novell settings that are making the difference. Most likely, when you try it on the other machine, it will fail.
If it doesn't ask for the link change and works, then it's the Excel settings on the other machine.

Let me know what happens

Then try:

* Get your file working again on your machine as you.
* Go to the other machine.
* Log in as YOU in Novell and as ADMIN for the workstation
* try the file and tell me what happens.

Is it possible for you to send me the Jobs and a working Timecard file? It would make it easier for me to see what's going on within the file but I would understand if you can't. My email is online@msantiago.net.

 

[combo]

Hi Janet,
I've traced your thread, but have no idea where to go. It seems that excel links are out of control.
So, as another thread, I would try to use data links to get data.
Here the scenario:
1. In the workbook 'Jobs' (server) add name to the range you want to search. Say it is JobsRange (to add name, go Insert>Name>Define... from main menu). Save the workbook.
2. In the 'TimeCard' workbook import data from 'Jobs' workbook. Go Data>Import external data>New database query. Select 'Excel files', close with OK. Find (select workbook dialog) 'Jobs' file on the server, point it and accept. Add whole table or selected columns to the right window, go next (filters, select if you wish), go next (sorting option), click 'Finish' if 'transfer data to excel' option selected. Place data to new worksheet.
3. The data should be in new sheet. Now customize it. You should see an 'External data' toolbar. if it is not active, select any cell within imported range. Click properties button. Shorten the data range name, select refresh data while opening file.
4. If necessary, hide this sheet or use VB editor to make this page completely invisible for the user (property 'Visible' set to xlVeryHidden).
5. Refer with VLOOKUP function to the data range in workbook 'Timeline': if the sheet name is Sheet1, data range name (#3) JobsData, use searching range =VLOOKUP(Sheet1!JobsData,...,...).

When opening 'Timeline' workbook, you will be warned about automatic query. You can decide if disable this message.
When you add a row to 'Jobs' data and extend JobsRange name, imported data in 'Timeline' will automatically extend
named output range.

Sorry for starting from scratch, but may be this will help

combo

 

[janetlyn]

Just a note to end this thread (hopefully). Santiago and I have been working on this off-site of the forum. Unbelievably, I think me sending him the bad worksheets, and then him sending them back fixed the problem. I am still not sure what he did that fixed them or if it was just getting it out of my server environment onto his to be "cleaned" and then back to mine, but the timecard he sent me seems to be working fine now. God Bless You Michael for your continued efforts on my behalf. You truly should be tipmaster of the day, week, month and year. Your ever faithful "dummy". Janet Lyn