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))
Improve this question
2
  • 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? Commented Feb 14, 2023 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) Commented Feb 14, 2023 at 16:41
Related questions
10
Excel formulas linked to other workbooks don't update automatically (without opening the source)
1
excel formula stopped working after saving and re-opening
1
Excel formulas don't update unless I click on the formula and press enter
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.