SOLVED - Hyperlink function works as Flash Fill but not as Dynamic/spill array
Hello all, I have tracker logs that I'm comparing to my bank and have solved all the issues so far, except for how to spill my Find & Go To hyperlink function. I have a complex formula in range $I$3 that will list all mismatches (bank transactions that are not in tracker and tracker logs that are not in bank) via spill and a data validation list in range $J$3:$J$1000 to select an action from. In range $K$3, I need a spill formula that will search the import sheets for the amount shown on the mismatch list, for each mismatch, then go to it upon click. In range $K$3:$K$1000, I currently have individual formulas that will check if the mismatch contains "Bank" and if so will display a hyperlink that extracts all text then reads the amount as a value, then matches it to the Tracker Import sheet. Otherwise, it will display a hyperlink that does the same thing but matches it to the Bank Import sheet. Logical Test (checks if it contains "Bank"): Value if True (creates hyperlink to the mismatch amount on the Tracker Import sheet): Value if False (creates hyperlink to the mismatch amount on the Bank Import sheet): This works perfectly fine but when I do it as a spill formula, the links either don't work or take me to the incorrect sheet/cell. Formula (Flash Filled range $K$3:$K$1000 - working): =IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"") Formula (spill from range $K$3 - not working): =IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3:I1000)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"") Notes: • Range 'Tracker Import'!$D$2:$D$1000 is an Amount column that lists logs from my Spending Tracker. This range is just values. • Range 'Bank Import'!$F$2:$F$1000 is an Amount column that lists transactions from my bank statement. This range is just values. • If the Mismatch List says an amount is not found in the Bank, that means it's an existing amount in the Tracker that shouldn't be there, and vis versa. I would highly appreciate any solution to make my current formula a spill formula. Thank you!
Performance issues with multiple slicers in "Analyze in Excel" (Power BI Live Connection)
Hi Excel Community, We are experiencing performance challenges when using "Analyze in Excel" connected live to a Power BI Semantic Model, particularly when we have a large number of slicers applied to a Pivot Table. Through our analysis, we've observed that when the "Visually indicate items with no data" option is enabled for these slicers, each slicer interaction seems to trigger at least two additional MDX queries. What's concerning is that these queries appear to run sequentially, meaning that with more slicers, the delay for each filter change becomes significantly longer. We wanted to ask the community if this sequential query execution for each slicer with "Visually indicate items with no data" is the expected behavior in the current implementation of "Analyze in Excel." Has anyone else encountered similar performance issues with multiple slicers in this scenario? If so, have you found any workarounds or best practices to mitigate these delays? We're particularly interested in understanding if there are alternative approaches to achieve the "visually indicate items with no data" functionality without incurring this sequential query overhead. Any insights or experiences you can share would be greatly appreciated. Thanks!Power Query Dropping Decimals with Accounting Format
I'm encountering what seems like a bug in Power Query. I have a table with data that contains numbers with more than 4 decimal places. When this data is in the Accounting format, Power Query is only picking up 4 decimal places, even if the Query is formatting the data is Decimal Number (Changed Type). When the data is formatted as Number, Power Query is able to pick up all decimal places (Some numbers have 10+ decimals) Is this normal behavior? I would like to use the Accounting format because it looks cleaner, but obviously I cannot sacrifice data accuracy. It is also very illogical to have a format labeled "Accounting" only hold 4 decimal places when passed through Power Query I did try restarting Excel multiple times, refreshing queries, the only thing that resolved it was changing the local format to Number.
Complex Employee Training Tracker
Before reading, I want to mention that I'm mostly looking for a function or feature that can evaluate and compare data from multiple tables, and how to organize my data around it. I've been using Excel to track the training of my company's employees for some time now, but we've been ramping up hiring and buying new equipment that requires specialized trainings, so I'm looking to inject more automation into this process. My old spreadsheet is filled with so many VLOOKUP's and conditional formats that I'm starting over from scratch and migrating important data. That said, I have a few really simple objectives that I have not been able to accomplish in the couple of days I've been working on it. At it's core, this workbook will include: A list of employees, with assigned responsibilities (multiple) and their personal information. A list of approved trainings, each of which is linked with specific responsibilities (emphasis on the plural). A training log where I manually assign required trainings and record dates when complete. Some form of analysis to see a list of approved employees for each responsibility and/or a list of each responsibility that an individual employee is approved for. The frequent addition of new trainings is key here. I want to make sure that, when a new training is developed either for all employees or for a specific piece of equipment, employees are removed from the approved list until they complete the training. I have been trying to brute-force things with pivot tables, but for the life of me I cannot seem to develop a system that can easily produce the data I'm looking for. Here's a brief outline of my vision: Select a specific responsibility > Produce a list of required trainings I'd like for it to show all prerequisite trainings for someone with that responsibility. For example, if the role is a robotic welder technician (Warehouse > Fabrication > Robotic Welder > Robotic Welder Technician) I would want it to produce the trainings required for Warehouse, Fabrication, Robotic Welders and Robotic Welder Technicians. Have an automated check of the training log to produce a list of the employees that have completed all of the required trainings for the selected responsibility. Lastly (and this is really just a pipe dream compared to the rest of what I'm trying to accomplish) it would save me a lot of time if training requirements were automatically assigned to employees based on the roles they are assigned to. That way, when an employee is assigned to a new responsibility, their training assignments will automatically reflect additional training requirements. I expect to be doing this part manually for now, though.
count some data
Hello My friend, Can anyone please inform me what is the Excel equation for the below details: I have 2 columns first one the state and it have two cells only with C or T....and the second column is for agent name which has many cells more than 3k...so i need to know like the cells on the right every agent how many total it have of c and t and how many separately. Thanks in advance.
Excel Data Model File Path Change
My boss created an Excel data model before I joined the company. He created the data model and queried the data from files within his personal drive. Now that I am here, he wants me to have access to these files and has moved them into a shared folder. The thing is, all of the queries now have the wrong file path. Is the only way to fix this to manually update the file path within the Source step of the power query editor? If there is an easier way, please let me know!
I need help with transforming data
I have been trying to move my data automatically instead of copy paste cells one by one, but because my data has lots of info, I'm failing in doing so. I added a picture, I only made 6 for the example but I have over 50 agents. I want this data to go automatically in the other format from the picture. So this other format of the table should show how many hours each of them has worked each day. As you can see I need to move the data from the first picture (different table formatting) to the other one. I have over 50 agents, and their time worked is put one by one based on the date. Now on the second one (the one I need to be filled automatically), the table is set on the way where I put the hours worked by agent, one by one for one month. How do I transform my data into that? What's the best formula to use? Please help!Urgent please help me on me geography coursework
I used a visual housing survey to assess how housing differs across 2 areas- i ranked houses in each area in 4 categories - noise, exterior appearance, cleanliness and house plot size. I want to use a statistical test but idk which or how - PLEASE HELP BEFORE IT IS TO LATE - DUE IN TOMORROW
Table visual is not filtered due to dax measure
Hi Team, I have a visual as below: when i filter company as below: Table should give me records for selected company and not any other records. But the table here shows all records which means the filter is not recognized. for example, the site and device slicer shows correct records for filtered company but table is not: I guess the problem is due to the below dax online status: This brings in all records irrespective of the selected slicer. Could you please help me resolve this? Please advise! PFA file here PR0442 - Heatsense Live_V1 - Copy.pbix Thanks in advance! SergeiBaklan
