2

Issue: As the title says, any time a LAMBDA formula is used in my spreadsheet it forces all other users who have the shared spreadsheet open to reopen the spreadsheet. With my brain's limited capacity, I am struggling to find anyone else struggling with this issue in any google search I've tried.

What I've tried: I've tried several methods to get the formula into the needed cells (thinking perhaps it was a macro that was breaking things) - regardless if I'm typing, copying/pasting, using VBA to write the cell contents, as soon as the cell contents are "locked in" every other user is forced to reopen.

Is this a known issue (that I just suck at googling) or "feature"? Has anyone else experienced this and/or found a workaround?

For use-case, and how I have things structured, I've included a link to a "neutered" spreadsheet that can be downloaded (making it live might break it as soon as someone else makes changes something, and I wouldn't have any way to ensure everyone has an original copy to look at) https://www.dropbox.com/s/35gbt7shulx5ckt/PFab%20-%20error%20reproduction.xlsx?dl=0

In the O-T columns I would place the formulae =orderDate or =customLead(numberOfDays) (where "numberOfDays" is an integer entered, not a formula). Either of these will force the spreadsheet to reopen for other users.

Here are the custom formulae I'm using:

  • orderDate =IF(SFD="","",recurse(sub,daysOff))
  • SFD =PFAB!$A16 (row changes depending on cell selected)
  • recurse =LAMBDA(x,y,IF(OR(x=y,WEEKDAY(x)=1,WEEKDAY(x)=7),recurse(x-1,y),x))
  • sub =LAMBDA(x,y,IFERROR(x-y,TODAY()))(SFD,leadtime)
  • daysOff =OFFSET(daysOff!$A$1,,,COUNTA(daysOff!$A:$A),1) (dynamic range with stat holidays)
  • leadtime =INDEX(LeadTimeTable[Lead Time],COLUMN()-14) (row of table selected based on column formula is in)
  • customLead =LAMBDA(numberOfDays,recurse(SFD-numberOfDays,daysOff))
jeranon
  • 433
  • 2
  • 14
  • What does it mean the spreadsheet is shared? Is it shared on Microsoft Office 365, on Dropbox, on a local mounted network drive? Are users opening it using the MSO365 web interface, or using the desktop Excel app? i.e. what are the precise steps to reproduce the issue? – ttarchala Feb 14 '23 at 12:37
  • In this case, I'm meaning "shared" by saying it's being shared using Microsoft Office 365. The only reason I'm linking to a Dropbox file is so the error is reproduceable without me needing to maintain a shared spreadsheet anytime someone tries something or tweaks it. Users are using the desktop Excel app. As soon as a cell is written with a LAMBDA formula (specifically in this case "orderDate" or "cusomLead" it forces anyone else with that spreadsheet open to reopen their instance of it (yellow bar at the top saying changes cannot be saved, must reopen) – jeranon Feb 14 '23 at 16:41

0 Answers0