locked
Sharepoint online excel mactro spreadsheet not calculating RRS feed

  • Question

  • I have a frustrating issue. We have a macro enabled spreadsheet on sharepoint online that does some calculations upon opening (using auto_open macro). We have been using this for over a year without any issues. Now when I open it, I get a read only error (see below). If I discard changes it will open fine but will not do the calculations (but no error message). I have a backup copy that runs fine in a non-sharepoint folder. So I deleted the file on sharepoint and uploaded my backup copy. But when I open it I get the same read only error. Why??? How do I get rid of that error?

    Note: I double-checked the formula tab and it IS set to calculate automatically.

    Thanks.

    Sunday, May 17, 2020 5:16 PM

Answers

  • Fixed the issue. I needed to use INDIRECT in the formula like below. Once I did that the range did not change.

    =IF(ISNA(VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),INDIRECT("WeeklyHRHistory!A$1:$ZZ$9000"),8,FALSE)),0,VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),INDIRECT("WeeklyHRHistory!A$1:$ZZ$9000"),8,FALSE))

    • Marked as answer by J-Bal Friday, May 22, 2020 3:20 PM
    Friday, May 22, 2020 3:20 PM

All replies

  • Hi J-Bal,

    Quote from Microsoft, we couldn’t create, run, or edit VBA (Visual Basic for Applications) macros in Excel for the web, we can open and edit a workbook that contains macros.

    For the read only issue, please check whether you have libraries with Checkout, Required, or Validation columns or metadata, or when Draft Item Security is set to either Only users who can edit or Only uses who can approve items in Version Settings of the library, these items will be synchronized as read-only.

    You could also open any other office files to compare if they are in Read-Only mode.

    More information about SharePoint files open as read only:

    https://docs.microsoft.com/en-us/sharepoint/troubleshoot/lists-and-libraries/files-open-as-read-only-and-cannot-check-in-or-out

    Best regards,

    Julie


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, May 18, 2020 2:20 AM
  • I fixed the read-only issue by discarding the changes and re-saving. Then I came across a different issue. The formulas I had set up changed! I had it doing an VLOOKUP from A$1:$ZZ$9999 (sheet will always have less than 9000 rows but I put that in to make sure I get everything). After I saved it I looked and the formula had changed to A$1:$ZZ$1126 which IS less than the # rows and the reason why it wasn't picking up the data correctly in the formula. Why does it do this and is there a way to prevent it?

    Note: I put it back to 9999, ran it again and now it says 8955.

    Thanks.

    Monday, May 18, 2020 12:47 PM
  • Hi J-Bal,

    Whether the issue only occurs in office online?

    Could you calculated the formula correctly in desktop app?

    Please try it and post back.

    Best regards,

    Julie


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Wednesday, May 20, 2020 9:38 AM
  • Hi J-Bal,

    Is there any progress on this issue?

    Please feel free to reply.

    Best regards,

    Julie


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Friday, May 22, 2020 8:19 AM
  • Fixed the issue. I needed to use INDIRECT in the formula like below. Once I did that the range did not change.

    =IF(ISNA(VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),INDIRECT("WeeklyHRHistory!A$1:$ZZ$9000"),8,FALSE)),0,VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),INDIRECT("WeeklyHRHistory!A$1:$ZZ$9000"),8,FALSE))

    • Marked as answer by J-Bal Friday, May 22, 2020 3:20 PM
    Friday, May 22, 2020 3:20 PM
  • Hi J-Bal,

    Congratulations on solving this issue and thanks for sharing your solution.

    It will do great help to those who meet the similar question in this forum.

    Thanks again for your contribution.

    Best regards,

    Julie


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, May 25, 2020 1:11 AM