Forum Widgets
Latest Discussions
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!
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?Currency Formatting adding additional periods to number
Hi, This has started happening on Excel and its baffling me so I thought I'd reach out to the community to see anyone can help. This is Excel v2606 (M365) with latest updates. Issue: I have a new work book and in a cell I simply add the number 20 I right click the cell with the 20 in it and select "Format Cells.." and change it to currency and this happens If I go back into "Format Cells" the selection has moved from Currency to Custom and it shows this: And clicking on Currency the format is shown as this: No matter what I do I can't get it to format correctly, if I change the format back to "General" it goes back to being 20. I've tried doing an online repair and that hasn't had any affect. Any help gratefully received. Rob
