0

I have an Excel file on SharePoint. Multiple people edit said file. Offline and online.

Excel unfills all calculated cells (VLOOKUP and MATCH) that depend on other sheets in the same workbook at random. So if you would open or reload that file, suddenly all the cells that use VLOOKUP or MATCH would be blank. Upon further investigation nobody changed anything or deleted anything. It just happens randomly. Now it has happened 2 weeks ago and just today.

I have an interim solution that is to go to a cell with a formula, hit return, then click „overwrite all cells in this column with this formula“ and then click undo to prevent custom cell content to be overwritten. Everything is back to normal afterwards. I talked to a colleague, he has the exact same issue also totally at random. He also encountered this on files that are not being edited by multiple people but himself. So it is not dependent on the file itself.

Could the language play into this problem? On some PCs Excel is in English and on some in German.

=IFERROR(IF(ISNA(VLOOKUP(D185,Sheet1!$B$3:$D$616,3,FALSE)),(VLOOKUP(D185,'Sheet2'!$B$3:$D$22200,3,FALSE)),(VLOOKUP(D185,Sheet1!$B$3:$D$616,3,FALSE))),"")
arun v
  • 852
  • 7
  • 19
LineLineLine
  • 9
  • 1

1 Answers1

0

Seems like your sheet isn't calculating upon open. See if this link is of use to you. https://superuser.com/questions/448376/what-is-the-excel-hotkey-to-re-calculate-all-formula-in-sheet

Also check your program settings and see if auto-calculate sheets is enabled. Don't know from the top of my head where it is located.

Alternative create a small vba function to force recalculate on workbook open. Getting Excel to refresh data on sheet from within VBA

Plutian
  • 2,276
  • 3
  • 14
  • 23
  • Thank you for your answer! The sheet is always auto calculating, except for these few occurrences like 2 weeks ago and today. I come to think that this might be a SharePoint problem... – LineLineLine Aug 23 '19 at 12:41