linking excel spreadsheet from network

Discussion in 'Microsoft Excel Misc' started by Sue, Nov 28, 2007.

  1. Sue

    Sue Guest

    I am linking 2 spreadsheets using information from 9 spreadsheets from 3
    different locations. I have networked the 9 spreadsheets and networked them
    to my desk top. I used the fully qualified addresses to link each cell, (not
    X,Y or Z), however it still will not auto update my spreadsheet. I am using
    Excel 2003. Any ideas?
     
    Sue, Nov 28, 2007
    #1
    1. Advertisements

  2. Sue

    Conan Kelly Guest

    Sue,

    What do you mean that it won't "auto update"? Are you getting error
    messages? Are you getting values, just not the ones you expect?

    Are you the only one that will be using these files?

    What do you mean by:

    > I used the fully qualified addresses to link each cell, (not
    > X,Y or Z)...


    Do you mean in your cell, the formula looks something like this:

    ='\\Server\Share\SubFolder\[File Name.xls]Sheet Name'!D6

    If that is the formula that you are entering, then I don't see any reason
    why you would need to "network" each spreadsheet to your desktop.

    If you "linked" to the other file (while it was already open) by typing an
    equal sign and then switching over to it and navigating your way to the sell
    you want, that may cause problems depending on where (network location) the
    file was opened from, who all will need to open this file, and what computer
    you (or others) will open this file on.

    Please pose more info so we can be of more help,

    Conan


    "Sue" <> wrote in message
    news:...
    >I am linking 2 spreadsheets using information from 9 spreadsheets from 3
    > different locations. I have networked the 9 spreadsheets and networked
    > them
    > to my desk top. I used the fully qualified addresses to link each cell,
    > (not
    > X,Y or Z), however it still will not auto update my spreadsheet. I am
    > using
    > Excel 2003. Any ideas?
     
    Conan Kelly, Nov 28, 2007
    #2
    1. Advertisements

  3. Sue

    Sue Guest

    Conan,
    Thanks... to clarify, I networked the drive to my desktop... in three
    separate drives (X, Y and Z) There were several spreadsheets in each drive
    that came from 3 different servers. I built one spreadsheet to gather
    information from each of the spreadsheets that all updated twice daily. I
    set up a separate spreadsheet for the morning and another for the afternoon.
    At first I linked the cells to the drive number (x, y or z) corresponding
    that held the source sheet that I needed for each cell. That did not work -
    it would give me an error that the source could not be found. Then I did
    some research and found that I needed to link to a fully qualifed name, e,g,
    \\server\folder\source) rather than by a drive letter. It still is not
    working, I get an error that it can't find the source.

    When I go into the error box to find the solution, it will allow me to find
    the source, but when I go into the correct link, it comes back as error
    again. I am beginning to think that it may be a glitch with the 2003 version.
    Is my explanation clear? It is a bit hard to explain. Thanks for your
    help.

    Sue
    "Conan Kelly" wrote:

    > Sue,
    >
    > What do you mean that it won't "auto update"? Are you getting error
    > messages? Are you getting values, just not the ones you expect?
    >
    > Are you the only one that will be using these files?
    >
    > What do you mean by:
    >
    > > I used the fully qualified addresses to link each cell, (not
    > > X,Y or Z)...

    >
    > Do you mean in your cell, the formula looks something like this:
    >
    > ='\\Server\Share\SubFolder\[File Name.xls]Sheet Name'!D6
    >
    > If that is the formula that you are entering, then I don't see any reason
    > why you would need to "network" each spreadsheet to your desktop.
    >
    > If you "linked" to the other file (while it was already open) by typing an
    > equal sign and then switching over to it and navigating your way to the sell
    > you want, that may cause problems depending on where (network location) the
    > file was opened from, who all will need to open this file, and what computer
    > you (or others) will open this file on.
    >
    > Please pose more info so we can be of more help,
    >
    > Conan
    >
    >
    > "Sue" <> wrote in message
    > news:...
    > >I am linking 2 spreadsheets using information from 9 spreadsheets from 3
    > > different locations. I have networked the 9 spreadsheets and networked
    > > them
    > > to my desk top. I used the fully qualified addresses to link each cell,
    > > (not
    > > X,Y or Z), however it still will not auto update my spreadsheet. I am
    > > using
    > > Excel 2003. Any ideas?

    >
    >
    >
     
    Sue, Nov 29, 2007
    #3
  4. Sue

    Conan Kelly Guest

    Sue,

    It shouldn't matter if you are linking to X:\ Y:\ or Z:\ compared to
    \\server\folder\source. They should work either way.

    The only reason it is good to use \\server\folder\source over a mapped
    network drive is if the workbook you are creating is going to be used by
    other users. They may not have the same network shares mapped to the same
    drive letters on their computers. If you use \\server\folder\source, then
    other users will be able to open the files and update the values (depending
    on their permissions on the network shares). If you are the only one using
    this file, then you could probably get away with X:\ Y:\ & Z:\.

    The other thing I noticed is that you have many worksheets on 3 different
    servers. You are creating one worksheet/workbook (linking to all these
    others) to bring all this info into one place. Then you are creating 2 more
    (a morning and an afternoon book) to link to this "middle man". My only
    concern with that is that when you open the morning or afternoon book, it
    will go find the middle man and pull data from that, BUT I DON'T KNOW if the
    middle man will automatically update its values from all of the other
    worksheets before sending values onto your morning/afternoon book. I don't
    know enough about XL to know what the process is here.

    Some other questions: Are all of the first level files password protected?
    Do you have permissions on all of them? When you open your "middle man"
    file, will it update values from all of the first level files?

    Once again, I don't have experience in all of these different scenarios, so
    I don't know for sure what exactly the problem could be.

    Maybe one suggestion is, if your "middle man" is working, but the
    morning/afternoon files are not working.....cut out the middle man! Maybe
    do a morning and an afternoon sheet in the middle man and eliminate the 2
    separate files linking to the middle man.

    If anyone else more knowledgeable than me knows what might be going on here,
    please feel free to chime in.

    HTH,

    Conan




    "Sue" <> wrote in message
    news:...
    > Conan,
    > Thanks... to clarify, I networked the drive to my desktop... in three
    > separate drives (X, Y and Z) There were several spreadsheets in each
    > drive
    > that came from 3 different servers. I built one spreadsheet to gather
    > information from each of the spreadsheets that all updated twice daily. I
    > set up a separate spreadsheet for the morning and another for the
    > afternoon.
    > At first I linked the cells to the drive number (x, y or z) corresponding
    > that held the source sheet that I needed for each cell. That did not
    > work -
    > it would give me an error that the source could not be found. Then I did
    > some research and found that I needed to link to a fully qualifed name,
    > e,g,
    > \\server\folder\source) rather than by a drive letter. It still is not
    > working, I get an error that it can't find the source.
    >
    > When I go into the error box to find the solution, it will allow me to
    > find
    > the source, but when I go into the correct link, it comes back as error
    > again. I am beginning to think that it may be a glitch with the 2003
    > version.
    > Is my explanation clear? It is a bit hard to explain. Thanks for your
    > help.
    >
    > Sue
    > "Conan Kelly" wrote:
    >
    >> Sue,
    >>
    >> What do you mean that it won't "auto update"? Are you getting error
    >> messages? Are you getting values, just not the ones you expect?
    >>
    >> Are you the only one that will be using these files?
    >>
    >> What do you mean by:
    >>
    >> > I used the fully qualified addresses to link each cell, (not
    >> > X,Y or Z)...

    >>
    >> Do you mean in your cell, the formula looks something like this:
    >>
    >> ='\\Server\Share\SubFolder\[File Name.xls]Sheet Name'!D6
    >>
    >> If that is the formula that you are entering, then I don't see any reason
    >> why you would need to "network" each spreadsheet to your desktop.
    >>
    >> If you "linked" to the other file (while it was already open) by typing
    >> an
    >> equal sign and then switching over to it and navigating your way to the
    >> sell
    >> you want, that may cause problems depending on where (network location)
    >> the
    >> file was opened from, who all will need to open this file, and what
    >> computer
    >> you (or others) will open this file on.
    >>
    >> Please pose more info so we can be of more help,
    >>
    >> Conan
    >>
    >>
    >> "Sue" <> wrote in message
    >> news:...
    >> >I am linking 2 spreadsheets using information from 9 spreadsheets from 3
    >> > different locations. I have networked the 9 spreadsheets and networked
    >> > them
    >> > to my desk top. I used the fully qualified addresses to link each cell,
    >> > (not
    >> > X,Y or Z), however it still will not auto update my spreadsheet. I am
    >> > using
    >> > Excel 2003. Any ideas?

    >>
    >>
    >>
     
    Conan Kelly, Nov 29, 2007
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. waynebouwmmct
    Replies:
    0
    Views:
    210
    waynebouwmmct
    Nov 5, 2003
  2. rband
    Replies:
    4
    Views:
    1,633
    David McRitchie
    Aug 5, 2004
  3. Jugglertwo
    Replies:
    0
    Views:
    360
    Jugglertwo
    Sep 12, 2008
  4. Mike
    Replies:
    13
    Views:
    414
  5. vennesse

    Linking 2 spreadsheet using data from 1st spreadsheet

    vennesse, Feb 11, 2009, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    179
    Roger Govier
    Feb 11, 2009
Loading...

Share This Page