Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel 2007 & sharepoint changing formulas

  • 12-08-2009 2:54pm
    #1
    Taltos
    Registered Users, Registered Users 2 Posts: 20,830 ✭✭✭✭


    Hi,

    hoping here someone here can help.

    I have a file on sharepoint with multiple links to other files on var sharepoint files.
    When I edit it directly in sharepoint all is good.
    However - if I check it out to my PC or save a personal copy then all the formulas with the links get automatically updated.

    Searching the web I have come across ref to replacing the formulas with UNC - but have no idea of that.

    Here is an example
    Before:
    =VLOOKUP(C128,'http://sharepoint address /sites/Group Folder/[Filename.xls]Sheet1'!$A$1:$S$593,11,FALSE)

    After:
    =VLOOKUP(C128,'C:\sites\Group Folder\[Filename.xls]Sheet1'!$A$1:$S$593,11,FALSE)

    As you might imagine this is really annoying as it means for the moment we can only update the book online


Comments

  • #2
    Ginger
    Registered Users, Registered Users 2 Posts: 2,931 ✭✭✭Ginger


    I know a workaround for this and its to copy the file using explorer view.

    Other than that you can map the file shares in Sharepoint to UNC paths and allow a fix that way, but its painful.

    I dont know how/why exactly this is happening but its out there


  • #3
    Taltos
    Registered Users, Registered Users 2 Posts: 20,830 ✭✭✭✭Taltos


    Ginger wrote: »
    I know a workaround for this and its to copy the file using explorer view.

    Other than that you can map the file shares in Sharepoint to UNC paths and allow a fix that way, but its painful.

    I dont know how/why exactly this is happening but its out there

    Thanks Ginger,
    Yup so I can check it out or lock it to prevent other users updating it and then open it directly in sharepoint and save it down, just tried that so a big thank you for this - but I have a bug about having to do these extra steps.

    My main worry is that one of the other users of the file will unknowingly change all the formulas and I will have to do another cleanup.

    Can you elaborate on mapping the file shares to UNC paths?
    I have not used UNC paths before and can find very little on my searches on this except in relation to macros - since these are embedded formulas I am really scratching my head here.

    Thanks


  • #4
    Ginger
    Registered Users, Registered Users 2 Posts: 2,931 ✭✭✭Ginger


    You can map shares in SharePoint like you would map a drive in Windows and then you can use that path in your Excel formula

    UNC means \\server\share\file usually..


  • #5
    Taltos
    Registered Users, Registered Users 2 Posts: 20,830 ✭✭✭✭Taltos


    Thanks Ginger,

    I will see what I can do on mapping the shares in Sharepoint.

    Thanks again


Advertisement