Working with Arrays of Ranges
OK, so strictly speaking an array of ranges is an error in Excel right from the outset. However, I set up an array of tables contained within the named Range 'allocatedRange', here shown with a red outline. I then set up a function that would return the range corresponding to a country SelectDataλ = LAMBDA(country, LET( countries, FILTER(TAKE(allocatedRange, , 1), markers = "Country:"), recordNum, FILTER(SEQUENCE(ROWS(allocatedRange)), markers = "Country:"), recordEnd, VSTACK(DROP(recordNum - 1, 1), ROWS(allocatedRange)), rangeRows, recordEnd - recordNum, countryArrϑ, MAP( recordNum, rangeRows, LAMBDA(start, rows, LAMBDA(TRIMRANGE(TAKE(DROP(allocatedRange, start), rows)))) ), XLOOKUP(country, countries, countryArrϑ)() ) ) The start and row count of each table is determined by using the string "Country:" as a marker and differencing row numbers. A combination of TRIMRANGE/TAKE/DROP picks out each range in turn and assembles them into an array by turning the range references into thunks (TYPE=128). The function SelectDataλ is used to look up any specific country and return the corresponding range. To demonstrate that the function indeed returns ranges, the COUNTIFS function is used to count the number of cities within each country that have a population exceeding 100 000. = LET( countries, FILTER(TAKE(allocatedRange,,1), markers="Country:"), countLarge, MAP(countries, LAMBDA(country, COUNTIFS(TAKE(SelectDataλ(country),,-1), ">1000000") ) ), HSTACK(countries, countLarge) ) The point of this post is to introduce the idea of treating tables as elements of an array, so allowing further tables to be inserted or removed dynamically. TRIMRANGE ensures that each range is sized correctly and MAP is used to analyse each table in turn. Whilst Excel throws hissy fits at being asked to store arrays of arrays, arrays of ranges, or nested arrays, it is perfectly happy to store arrays of functions. When evaluated, each element of the function array is perfectly free to return an array or even a range. The effect is to permit Excel to process 'arrays of Tables' faultlessly.Table to Range issue
Hello everyone, Could use some help. I have a workbook with many TABS. The main sheet in the workbook is a TABLE. This TABLE is Named "Room_Roster". When I made this sheet a Table, I did not realize it was going to mess up something being displayed on the room roster that was coming from merged cells on another sheet. Because of this I decided to convert the TABLE back to a RANGE. Upon doing this, another TAB was created called "Room_Roster" as a range. My issue is, while trying to perform a mail merge, the mail merge is looking at the Range Sheet "Room_Roster" instead of the Table defined as "Room_Roster". Thinking this was an issue, I renamed the RANGE to Room_Roster Non Table but the mail merge is still seeing the range as the data source even though I renamed it. I tried to delete the Room_Roster defined name from the Name Manager but it would not let me. I am stuck here and do not know what to do about this. Can I please get some ideas of what to do? Thank you, Carl
SharePoint opens different versions of files in the desktop app and the browser
Hello, I am experiencing two problems with Excel files on SharePoint. I have File A, which is linked via formulas to File B (File A serves as an input for File B). I am using Excel 365. Problem 1: Sync and Loading Issues with File A When I open File A using the desktop app, Autosave is enabled, and I can see other users working in the file (their cursors are visible). However, I do not see the changes they make. When I open the same file in a browser, I do not see myself listed as working on the desktop app, nor do I see the changes I’ve made in the desktop app. Additionally, when I try to close the desktop app, I receive the error message shown in the screenshot, but the loading process never completes. If I cancel the loading process and reopen File A in the desktop app, it displays the version I had previously saved via the desktop app. Furthermore, if I download the file from SharePoint, it matches the browser version. When working on Files A and B simultaneously in the desktop app, changes made in File A correctly affect File B. Problem 2: File B is based on desktop version of File A When I open File B, it displays the input from the desktop version. However, the other user sees the input from the browser version of File A.
Usage data for Personal Bookings/Bookings with me
Is there a way (powershell cmdlet or graph call) to get a count of how many users in a tenant have created/enabled Personal Bookings? I'm not talking about shared Bookings calendars, but people who have turned on the "Bookings with me" feature. The reason I'm looking for this data is that right now we have some departments who are using other similar services, in some cases they are costing the org quite a bit of money. If we can demonstrate that a significant percentage of users are making use of this feature that we're already paying for, we have leverage to recoup some operating costs.
One of my Office 365 user, suddenly missing
One of company user suddenly missing from active users, and I can't find it on Deleted user, last night my team on New Zealand doing synchronization, I can see all user with onMicrosoft.com are on deleted users including my account, but I can still see my account on active users tab, but one of my user is suddenly gone, only his onMicrosoft.com are in deleted users tab, I cannot see his original account on deleted users.Mail Merge Dates Issue
I have an Excel Sheet that includes dates. I have created a Word Mail Merge Document that includes the merging of dates and other information from the Excel Sheet. In the merge, all data other than the dates are merging correctly. The dates are merging in as numbers, regardless of switches. I do not understand why I am having so many problems with this date issue. The date clearly shows as a date in Excel yet its merging in as numbers. No matter what I try, I cannot get the Date to merge in as the Date shown in Excel. How can I make this work. Carl
Calendar display in MS Bookings
Hi, Im trying to set a service up in Bookings. I can choose the dates when it is possible to make a reservation but the problem is that those dates are in february. Is there a way to only display available dates instead of the hole calendar and having to navigate to february? I have far too many request saying there is no availability because it displays the current month and they don't get that they need to navigate. Thanks,Turning off Email notifications....
Hi there, we have users that complaine about too much EMails send by the Planner App in teams. For instance, they do receive an email every time someone adds a comment to a task, where they are members. And they claim that this is only since last week or so. They want that to be turned off. But I cannot find where it could be located? Can anyone help. Thanks a Million Berthold
