Xlookup shifts if I add a column to the query
I have a query table that I'm referencing using an xlookup function on another table. When I add a column to the query, any xlookup gets shifted over one column. =XLOOKUP ("Value", Querytable[Column1],Querytable[Column4],"") but if I add a column in between 2 and 3 in the power query editor, when the table load my formula changes to =XLOOKUP ("Value", Querytable[Column1],Querytable[Column5],""). I thought doing formulas this way was supposed to prevent that. If I add a column to a table by right click and inserting a column things work fine and my formulas track with the proper column, but when its its added in power query it doesn't work and I can't fix 100+ formulas each time a column is added to a query that pulling data from hundreds of files, columns being added is inevitable.
Is it possible to do this?
Hey all! I currently use Smartsheet to track my trainees' progress and I love the way I can use Conditional Formatting in that program. Unfortunately, my company is going away from using Smartsheet, so I need to move all the information I have to an Excel document. I'm struggling with getting the Conditional Formatting to work the same way I have it in Smartsheet and I'm curious if it's even possible. Here is an image of the Template I will be using for each employee. My plan is to have a tab for each employee, just duplicating the template and renaming it to their name and adding their information. Here are the two things I'm struggling with getting set-up with Conditional Formatting: 1. In the Lvl column, I have a drop down that is linked to the Data tab where the icons on the left are images. I want to be able to select the "Assigned" option and have the A icon show up (this is currently set up) and then have the fill of the box turn to red. I'm struggling to get that to work properly. Currently, when I select the "Assigned" option in the drop-down menu, it just shows the "A" icon. 2. I also want to be able to have the "Date Completed" column change to a specific color if one of the checkbox columns (G, H, I, J, K) are checked (i.e. if G is checked, it turns a light brown, if H is checked, it's gray, if I is checked, then it's red, etc.). This is what it looks like in Smartsheet and how I'm hoping to get it to look in Excel: Thank you!AutoSave disabled when opening SharePoint-synced files from Finder after macOS Tahoe 26.6 update
Files stored in SharePoint Online and synchronized locally through OneDrive are opened as local documents when launched from Finder. Office applications display "Saved to my Mac" and AutoSave is turned off by default. However, opening the exact same files through Word/Excel > Open > Sites, or via SharePoint "Open in Desktop App", correctly identifies them as cloud documents. In that scenario, AutoSave is enabled and collaboration/version history features work as expected. Troubleshooting already performed: OneDrive reset and re-linked SharePoint library re-synced Signed out and back into both Office and OneDrive Removed Microsoft credentials from macOS Keychain and re-authenticated Recreated local OneDrive sync relationships Verified OneDrive File Provider extensions are enabled Verified Office applications and OneDrive are fully up to date Tested with newly created files and existing files Tested "Always Keep on This Device" with no change in behavior The issue appears to be specific to the Finder-to-Office launch path after upgrading to macOS Tahoe 26.6. Before upgrading to macOS Tahoe 26.6, opening the same SharePoint-synchronized files directly from Finder correctly preserved cloud document identity and AutoSave was enabled as expected. I discussed this issue in detail with Apple Support, but they quickly dismissed it, saying that the problem is not on Apple's side and that I should contact Microsoft instead.
Slicer not updating when source data is updated
Hello, I have a dashboard showing project data with multiple pivot tables. There are two slicers that are connected to all the pivot tables, Project ID and Project Status, these used to update when the source data changed but this is no longer the case and still doesn't update after refreshing the data. E.g. The Project Status of Project LIB682 changed from Active to Closed in the source data, however, if I select 'Active' in the Project Status slicer, LIB682 still appears in the ID slicer where it used to be greyed out to show it is not active. I have tried refreshing data and deleted and re-added the slicers but neither of these worked. When I first created the dashboard everything worked and I've not changed anything so would assume it should still be working. TIA
Lambda that uses INDEX with array arguments behaves inconsistently when saved in the Name Manager
Hello, I have encountered what appears to be inconsistent behavior when a particular type of lambda is saved in the Name Manager. The issue seems to occurs when the LAMBDA uses INDEX with either the row_num or column_num argument supplied as an array. The following is a minimal reproducible example: The formula "=LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4})" correctly evaluates to {1,2,4}. Now, save the lambda as TEST (or any other name) in the name manager. The formula "=TEST({1,2,3,4})" also spills the expected array {1,2,4}. However, when the result is passed to another function, the behavior changes. For example, "=SUM(TEST({1,2,3,4}))" and "=COLUMNS(TEST({1,2,3,4}))" both evaluate to 1. In contrast, using the lambda inline instead of the defined name produces the expected results: "=SUM(LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4}))" returns 7, and "=COLUMNS(LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4}))" returns 3. Unfortunately, I am unable to attach a workbook to this post. Additionally, the issue is not reproducible on every machine I have tested, although it is consistently reproducible in Excel for the Web. I am currently using Excel version 2607 on the Current Channel. Does this appear to be expected behavior, or is it a bug? In the meantime, what would be the best way to mitigate this issue? I have found two potential workarounds. The first is to prepend the result of the named lambda with a unary + (e.g "=SUM(+TEST({1,2,3,4}))"), which appears to force excel to treat the result as an array. However, when using a shared lambda library (as is the case for most of my team), users generally do not know the implementation details of each lambda, so it is difficult to determine when this workaround is necessary. The second approach is to avoid passing an array to the row_num or column_num argument of Index by using MAP. For example, the TEST lambda defined above can be rewritten as =LAMBDA(arr,MAP({1,2,4},LAMBDA(idx,INDEX(arr,1,idx)))), which causes it to behave as expected. However, I am concerned about potential performance implications. Intuitively I would expect that a single INDEX call with an array argument would be more efficient than multiple INDEX calls wrapped in MAP, but I do not know whether this is the case. Has anyone else encountered this behavior before?
