Excel 2000 Links

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

casey

Thread Starter
Joined
Nov 20, 1999
Messages
17
I do individual price sheets for my customers that are linked from 1 to 3 source sheets. These source sheets are my vendors price lists in xls format. Of course these change quite frequently. If I add or delete lines from the vendor files, the customer pricers linked to those files will go out of whack. In other words a cell reference on a source sheet of say D 13 will shift to D 14 if I add a new row. The customer price sheet now references the wrong price. I'm sure this did not happen with Excel 97, or I had 97 set to be able to shift as needed without having to manually make changes. Problem is I can't remember/figure out how to do this. Can anyone help me set this up so that when I have to update my source files (vendor price lists) i.e. - add a new row for a new part (or delete) the linked cells in the customer pricers will move also. Hope this makes sense!!
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Sounds like you are referencing cells when you should be doing VLOOKUP.

Suppose you have a column of part numbers, description, and prices on sheet2, cells A1 to C50

On sheet1, you've got an invoice and all you want to do is type in the part number into A5 and have it return the description and price into B5 and C5.

1. Select the columns A through C of sheet2. Hit insert-name-define. Type MyData.

2. In sheet1, cell B5, type:

=VLOOKUP(A5,MyData,2)

This means: find the value in A5 in the MyData range, and return the value next to it in the 2nd column of MyData.

3. In sheet1, cell C5, type:

=VLOOKUP(A5,MyData,3)

This means: find the value in A5 in the MyData range, and return the value next to it in the 3rd column of MyData.

Hope it helps. If you can't make it out, email the file(s) to [email protected]
 

casey

Thread Starter
Joined
Nov 20, 1999
Messages
17
Well Dreamboat, it's all 'French' to me. I think I misled everyone here by referring to sheets vs files. I'll try agn...specifically, I have 2 separate vendor price files, both excel. I have approx 50 other separate files in my "Customer Pricers" folder. Either 1 or both of the vendor files are linked to each of the 50 customer price files. (this was probably the wrong way to set it up, but that's where I am now) When I receive updates from the vendors, the new files will normally have new parts added and in some cases some deleted. I would like to be able to just do a 'save as' to the file name of my present vendor file and replace it, or at least manually delete or copy and paste appropriate changes. But if I do that the cell references do not shift with the corresponding price which has now shifted up or down, say from D10 to D11. Since my original post, I've been testing different scenerios and it appears my problem may be solved by simply protecting the customer price files. It seems then, that when I add or delete rows in the vendor price files the price cell in the customer file remains locked the the original price cell in the vendor file. Does this make sense. I'm going to continue testing this before I do a full scale change. If this sounds right to you, please let me know. Tks for the help.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Frankly, casey, if you have Access, I'm thinking you ought to get everything into that instead.

If you are smart enough to use Excel, you are smart enough to use Access--okay, maybe with a little guidance from your buds at TSG.

I'm sorry I can't quite picture what you're doing either. But certainly, with that many vendors and customers, and hence worksheets, you seriously ought to consider a database.

Is there some reason you haven't considered it?
 

casey

Thread Starter
Joined
Nov 20, 1999
Messages
17
Well, first of all, I purchased Office 2000 Small Business (assuming Access was part of it...but isn't). And secondly, I'm little wary of working a database...never done it before! It's a catch 22...I don't feel I have the time to do a crash course on learning a database (I'm a 1 man operation), and yet on the other hand, I'm probably using up that time anyway with the way I'm presently doing it! By the way, can Access be bought separately?
PS - when I look under Program Files/Office...there is an Access icon the says 4,573KB Application, but when I dbl click it I get and error..."Can't find the database you specified or you didn't specify a database at all". Am I missing something here??
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
The executable for my Access 2000 (Office 2000 SR-1) is:

Name: msaccess.exe
Date: 2/25/2000
Size: 4,573KB

Sounds like you've got it. Get the exact error, would you? Also, look for any files on your PC called *.mdw and remove them EXCEPT system.mdw. Try Access again. Try double-clicking the actual executable from the find box--see if that helps. Try double-clicking it while holding the left shift key down and see if that works. Maybe your shortcut is weird--if you successfully run the executable from the file itself, we'll get rid of that shortcut for you. Also make sure you have a system.mdw file and if you don't, I'll send you mine.

If any of them work, we'll get around that and make it work right.

You are right, by the way. We can most probably hook you up in a database in no time. The tables are the first thing you would build, and the most important. Don't start without us, LOL.

Also, I believe the SBE does come with Access. It's the Standard that doesn't.
 

casey

Thread Starter
Joined
Nov 20, 1999
Messages
17
Here's the exact error:

"Can't find the database you specified, or you didn't specify a database at all.
Specify a valid database name in the command line, and include a path if necessary."

Did 'Find' for *.mdw and came up with:

Northwind Taders Sample Company
Sbcmtmpl
System

Deleted all but System, double clicked System and just got a gray screen with no error. If I double click kthe msaccess in Explorer I get the gray screen with the error. Tried the shift key...no go.
 

casey

Thread Starter
Joined
Nov 20, 1999
Messages
17
Didn't like the sound of that "uh-oh"! (LOL) Does this mean I'm kaput? This article though refers to Access 97...I don't have 97 on my computer. The info in Explorer is the same as yours. Under 'Properties' the version is 9.0.3822. Anyway, let me know if there is anywhere to go from here. Tks...your help is appreciated.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Alright then. We already did the regserver. Still no go. Let's try one more thing:

Start-run and type "regedit" without the quotes, and hit enter.

Browse to:

hkey_current_user\software\microsoft\office\9.0\access

Right-click access folder and rename to Oldaccess, close out. Run Access.

If it's no good, the only thing I can suggest at this point is:

Uninstall Office 2000.
Run Eraser 2000 from your CD-ROM or from http://support.microsoft.com/support/kb/articles/Q219/4/23.ASP

AT this point, delete any shortcuts for Office apps. You can do this by doing a search for *.LNK files. Delete any that say Word, Excel, Access...that's just to get rid of any bad shortcuts you might have for Access mostly.

Reinstall Office 2000. When you reinstall, if you've got the room on your hard drive, hit the top icon on this install list and choose "run all from my computer". That way, you make sure all the wizards and everything are installed. Before installing ANY program, end task on all running programs except Explorer and Systray.

I really don't think you need to go backing anything up unless you stored stuff in places you shouldn't have. Well, okay. If you're using Outlook 2000, you may want to find the *.PST files on your hard drive and just put them in a folder on the C called Backups or something.

Wow. Hope it all goes well!
 

casey

Thread Starter
Joined
Nov 20, 1999
Messages
17
Welllll...here's what's happened:
Removed MSOffice disk 1 & 2 and did Erase2000 for disk 1 & 2. Only choices on reinstall were 'Install Now' or 'Customize'...chose Install Now. During install of Disk 1 received: C:Windows\System\MSRCLR40.DLL failed to register.
ERROR 1904 HRESULT - 2147023739. Contact your support personnel.

No go on 'Retry' so pressed Ignore. After Restart, at "Completing MS Office 2000 install, I got the same Error. Then installed Disk 2 (same choices). Then started to check my Excel and Word files...1st via the shortcuts on the desktop. Initially in Excel got "Microsoft Excel can't run this add-in. This feature is not currently installed. Would you like to install it now?Clicked OK & file opened. On next shortcut I tried to open It asked for the Small Business disk which I inserted. After that everything was fine. Checked other apps...all seemed OK except Simply Accounting. Kept saying a security file was missing and to restore. Anyway to shorten this, I got Simply going again, went to Explore and double clicked msaccess.exe and got the same msg as before I started all this.

Two things to note...the install CD only says Excel, Word, Publisher...no ref to Access, yet the exe file is there. Also, I did not delete my Excel & Word file shortcuts...just the 2 that said Excel & Word per your instructions, assuming that is what you meant. Soooo, guess we just continue on unless you know of a way for me to get Access on my computer without anything screwing up. I'm still sweating after this last try!!!) Tks agn Dreamboat.
 

casey

Thread Starter
Joined
Nov 20, 1999
Messages
17
Hey, Dreamboat....did you give up on me? I'm assuming there is no way to revise my source docs without screwing up the linked docs. And it appears Access IS NOT part of my Office 2000 Small Business. So, question still is...is there a way to get Access on it's own? Tks...your help is appreciated.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top