1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2000 Links

Discussion in 'Business Applications' started by casey, Jan 19, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. casey

    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!!
     
  2. Raphael

    Raphael

    Joined:
    Jun 20, 2001
    Messages:
    382
    Try naming the cells.
     
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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]
     
  4. casey

    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.
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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?
     
  6. casey

    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??
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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.
     
  8. casey

    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.
     
  9. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Okay, try this:

    Start-->Run and type:

    msaccess.exe /regserver

    Now, you may need to include the path to the executable, which I think you can find, right?

    If that doesn't work, can I assume you have the installation disk?

    ...a few seconds later....

    uh-oh:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;Q235279
     
  10. casey

    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.
     
  11. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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!
     
  12. casey

    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.
     
  13. casey

    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.
     
  14. ksuwyldkat

    ksuwyldkat

    Joined:
    Nov 9, 2000
    Messages:
    341
    Access is usually available by itself. You can check your local office supply and they should have it.
    Here is the office depot link
    www.officedepot.com
     
  15. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/65479

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice