EXCEL FORMULA HELP
I need help. I have attached the sheet I'm working on. I need to pull the data in column B (CLIENT) from the MAIN sheet to the respective individual sheet based on column E (PREPPER). For example, if the data in B4 says 'Jason Smith' and E4 says 'Jon'; I need it to go to the JON sheet in cell B4 but not show up on sheets JANE or MARLA. Help please!! Thank you! :)Logical function (AND, OR) in Dynamic Array/Spilled Range
Hello, I have a spilled function, (lets say in C1 I use SORT(B1:B10)) and I need to know if each cell in the spilled range is in the between a max value and min value, so I need the returned product to be also a spilled range. Let's say the max value is 7000 and min value is 3000, and I used the following function in cell D1: =IF(AND(C1#>=3000;C1#<=7000);"NORMAL";"ABNORMAL") I expect it to return it as a spilled range and fills D1 to D10, but it only returned the first row of the dynamic array. The returned product only fills D1 as "NORMAL" while D2 - D10 remains empty; it didn't return a spilled product. I tried to experiment with it, trying if the problem is in the IF function or in the AND function. I tried to do the IF and AND function separately on a spilled range, and it turned out the problem is with the AND function (the IF function returned a spilled range, while the AND only returned a single cell). I need the IF and AND function to return a spilled range product. Can someone help? Thank you in advance.
Excel Task list
Hi All, I am having an issue finding a way to organize my task list. I have a task list with multiple tasks that is color coded by task codes. For example if on position A1 I introduce the task code "C" then the task will appear in column "task 1" as Clean. Each tasks is color coded by priority, high priority turns red, medium turns the cell orange and low turns the cell yellow. However, beyond the color coding I want all the high priority cells (in red) to always go on column labeled "task 1" since it is the first task I want done. Right now, my issue is that depending on how the task code is entered, tasks will go to any column (i.e. Task 1, Task 2, Task 3, Task4 ,etc.) But I would like that all high priority go to one column (task 1), all medium go to another column (task 2) and all low priority go to task 3. How can I fix this issue? Thank you!Excel cell fill doesn't work.... !
Hi, I have all settings correct and still, randomly from time to time, for day or more, I cannot fill cells with data. It just taking first number from first cell and copy it ignoring numbers below, and only what I can see are formatting options (this where you change in ex. colour depends on data in the cell. Nothing seems to work, I have no updates pending, reset Excel or computer doesn't work. I use Excel daily and if this happen I have 1 or more days out of work possibility. What shall be fixed here? Help, please.How to transform wide-format data into the structure shown in 'Result' sheet using Power Query?
Hi everyone, I have a dataset in wide format in the "Data" sheet, and I’d like to reshape it using Power Query to match the format shown in the "Result" sheet. What the source looks like (Data sheet): Row 1 contains repeating column headers for measures like Sales, Purchase, Sell value, etc., grouped by date: | Product Brand | Product Name | Region | 01.05 Sales | 01.05 Purchase | ... | 01.06 Sales | 01.06 Purchase | ... | Note: The values in the file are randomly generated using a function, just for demonstration purposes. Rows where the Region column contains values like a1_1, a1_2, a1_3 are subregions, and the row with Region = a1 is the sum of those subregions for that product. What I want to achieve (Result sheet): I need each value in a separate row, with the following columns: Product Brand Product Name Region Date Measure Values Example: Product Brand Product Name Region Date Measure Values Brand A Product A_1 a1 2025-05-01 Sales 15 Brand A Product A_1 a1 2025-05-01 Purchase 22 Brand A Product A_1 a1 2025-05-01 Sell value 32 The Excel file contains two sheets: "Data" – raw data "Result" – expected outcome Can anyone help me write the proper Power Query steps (M code) to achieve this transformation? Thank you in advance! https://docs.google.com/spreadsheets/d/1nMz_TyXRSQNelq-cbUDfSf8ekKezGCTn/edit?usp=sharing&ouid=109036053433195029380&rtpof=true&sd=true
Formula 4 week rotation excluding Fridays and weekends
Hi. I'm creating an employee work calendar. I need to formulas. One i have it but the other one i cannot figure it out. Employee A works from home, called W, one day a week. Week 1 on a monday, week 2 on a tuesday, week 3 on a wednesday, week 4 on a thursday, week 5 on a monday and so on. Fridays not available and saturdays and sunday are weekends. My formula only works if the first of the month is a monday. So i cannot do another month plus i cannot figure it out for employee B when the working from home is a Tuesday for week 1, can someone tell me what i'm doing wrong?
Better model to improve the performance calculated table/Dax measures
Hi Team, Now, I have an issue with Data model I have developed. Attached is the model i use for my power bi reports that contains 3 dashboards. Invoice quality is one of the dashboards. Below are the requirements to be created for visuals: For the Data[DBName-Point_Id] & Data[Date] columns from Data table, we need to find the status(Dax) as below: *Credit Cancels Invoice -- Number of Credits = Number of Invoices && net cost = 0 *Invoice-Credit Value Mismatch -- ISEVEN(Net invoices) && Net cost <>0 *Multiple Credits -- Number of Credits > Number of Invoices && Net cost <>0 *Multiple Invoices -- Number of invoices >1 && Number of invoices >Number of Credits We need to use this Status dax in visual y-axis of stacked bar chart and use the Count of Dates from Data table as x-axis: Number of Credits,Number of invoices, Net invoices and Net cost can dax or a calculated table columns. Currently, we have created a calculated table using dax logic as below: Invoice Issues = CALCULATETABLE ( SUMMARIZE ( Data, Data[DBName-Point_Id], Data[Date], "Invoice Issue", SUM ( Data[Invoice / Credit] ), "Number of Invoices", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] > 0 ), "Number of Credits", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] < 0 ), "Net Cost", SUM ( Data[Cost] ) ), Data[Source] = "Invoice" ) Based on the above table, Status calculated column in created in same table: Status = IF ( 'Invoice Issues'[Number of Invoices] > 1 && 'Invoice Issues'[Number of Invoices] > 'Invoice Issues'[Number of Credits], "Multiple Invoices", IF ( 'Invoice Issues'[Number of Credits] > 'Invoice Issues'[Number of Invoices] && 'Invoice Issues'[Net Cost] <> 0, "Multiple Credits", IF ( ISEVEN ( 'Invoice Issues'[Net Invoices] ) && 'Invoice Issues'[Net Cost] <> 0, "Invoice-Credit Value Mismatch", IF ( 'Invoice Issues'[Number of Credits] = 'Invoice Issues'[Number of Invoices] && 'Invoice Issues'[Net Cost] = 0, "Credit Cancels Invoice", BLANK () ) ) ) ) This calculated table is then connected to Points and calendar table DBName-Point_id(points table) --DBName-Point_id(Invoice Issues table) Date (Calendar table) -- Date (Invoice Issues table) This setup causes performance issues especially Status column. Is there any better ways to model this to improve the performance? please let me know if you need further info! PFA file here https://1drv.ms/u/c/cfada767f73d87ed/EVqI42tWerxPhE8UOnAYLOQByuAnNCKVcGYV2E5d2Ha5SA?e=jTPJl8 Thanks in advance! SergeiBaklan
