Forum Widgets
Latest Discussions
Check symbol in drop down list
I want to have the check and cross symbol appear in the drop down list. I have created two cells, one with the check symbol (Wingdings font) and cross symbol (Wingdings font). When are create the drop down list the symbols appear as "ü" and "û". is there a way to have the list show the symbols?jdinn76Feb 19, 2025Copper Contributor5.7KViews0likes3CommentsCreating Logic to find whether columns in one table matches other columns in other table
Hi Team, I have 2 tables: Door table as below: Store Status table as below: They are modelled as below (let me know if we can better model it-suggestions are welcomed): Now, I need to create a logic(Breach) to find when Door Table - Status column being Open when Store Status table - Status column being Closed (indicated by - 'C'). During this logic, we have to make sure it satisfies below conditions: DateTime column of Store Status table should match the createdon column of Door table Store id column of Store Status table should match the siteid of Door table Output should return 1 if the conditions meets else 0. Could you please help me create a logic for this? PFA file here B&M.pbix Thanks in advance! SergeiBaklanExcellove15Feb 19, 2025Iron Contributor87Views0likes2CommentsHow to return counts of text cells across multiple worksheets in same workbook?
I'm working in Excel 365. Windows environment. Working in the Excel app but will be posting to a shared drive for multi-user data entry. 38 worksheets, one for each office. What I need is to be able to return the total # of records in the data set that are marked as 'Complete', 'Pending' and 'Urgent' (from a drop-down is one cell of each record), by office and the Rep's name that made the entry (also from drop-down). I would also like to be able to return the total entries across all worksheets by Rep Name, so I can see which offices/Reps are making the most/least entries. I'm not very savvy with using Power Query. I've tried to do it, but I think it requires that there are no blank cells in the records. It keeps making tables in the query that don't actually exist. Tried it using 6 worksheets, just to test it, and the appended data set contains way more tables that I added. I'm not bad with pivot tables. If anyone has an idea of how I could accomplish this, I sure would appreciate it! Thanks in advance for any assistance. :)Marcus_BoothFeb 19, 2025Brass Contributor153Views0likes16Comments"Sorry , something went wrong"
Can anyone help me with this error? In one of the Excel online workbook, which has a lot of rows and links. since around 2 weeks ago, everytime anyone double clicks any cells with hyperlink, it will show this error message afterwards and if you press OK the workbook will reload. Thank you.ricomulyadiFeb 19, 2025Copper Contributor66Views0likes2Comments- M365ConFeb 19, 2025Occasional Reader22Views0likes1Comment
Concatenate or vlookup or something else?
Hello! My first post and I'm not even really sure what formula I am looking for, but I'll try to explain what I need... Along the top of my spreadsheet are the dates of the month. The first column is a list of driver violations. I'm using TRANSPOSE and XLOOKUP to grab the dates of each violation, however, some violations happen on several consecutive days. Instead of lising several consecutive dates in a column, is there a way to return the date of the first violation in a column and the last consecutive date in another column, or return for example October 1 - October 5 in one column? Thank you for any help you can offer! Dawn-Mariedmgiles0528Feb 19, 2025Copper Contributor57Views1like3CommentsPower Pivot Tables not Refreshing
I have a problem with Power Pivot in Excel. When I try to refresh the table it hangs and never completes. It was working fine yesterday. I have tried the following: 1. Check for Data Source Issues Ensure that the source data is accessible and hasn't changed (e.g., moved, renamed, or permissions altered). 2. Check for Large Data Volume or Memory Limit Try refreshing a smaller dataset first (e.g., filter down data in the source before loading). 3. Restart Excel & Try Again Close Excel completely (including any background processes in Task Manager) and reopen it.CliveJLFeb 19, 2025Occasional Reader46Views0likes1CommentAuto-Populating Data From One Sheet To The Other
My structure is 2 separate tabs in the excel sheet. In one sheet, my "Devops Tasks" sheet, I have a status of "COMPLETE", and currently, everything besides 2 are set to "INCOMPLETE". When I set the status to "COMPLETE" my formula populates this data over here to my other sheet which is labeled as "Handover Report" within my "Completed DevOps Tasks This Shift": Right now though, it only populates as #NAME? - What exactly am I doing wrong here in this formula? =@IF(COUNTIF('DevOps Tasks'!C[6],"COMPLETE")>=4,INDEX('DevOps Tasks'!C[2],@AGGREGATE(15,6,@ROW('DevOps Tasks'!C[6])/(@'DevOps Tasks'!C[6]="COMPLETE"),4)),"") As a side note, but maybe not entirely relevant - one way I'm auto-populating is through what I do within JS: const completedTasksTable = handoverSheet.addTable({ name: 'CompletedTasks', ref: 'A' + (handoverSheet.rowCount + 1), columns: [ { name: 'ID', filterButton: true, width: 15 }, { name: 'Title', filterButton: false, width: 50 }, { name: 'Completed By', filterButton: true, width: 25 }, { name: 'Sprint', filterButton: true, width: 20 } ], rows: Array(10).fill().map((_, index) => [ { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!C:C,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` }, { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!D:D,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` }, { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!B:B,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` }, { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!A:A,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` } ]) }); const completedTasksHeaderRow = handoverSheet.getRow(completedTasksTable.headerRow?.firstRow || +1); if (completedTasksHeaderRow) { completedTasksHeaderRow.font = { bold: true }; completedTasksHeaderRow.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFF0F5FF' } }; } completedTasksRow.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF1E4D8C' } }; completedTasksRow.font = { color: { argb: 'FFFFFFFF' }, bold: true };ALawtonFeb 19, 2025Occasional Reader60Views0likes2CommentsExcel Formula Help - Text & Numbers
I would like to have a formula that will automatically change this sequence but can't figure it out, would greatly appreciate the solution as I cant find it searching either. The sequence is TP0285 / 02 / 25 the sequence to change is the 1st the number 285 to 286 and so on, the 02 would change as per the month, March is 03, April 04 and so on and the last digits would change annually. Thanks in advance.Gtur1976Feb 18, 2025Occasional Reader64Views0likes1CommentDealing with VSTACK with empty arrays
Hi all Question - do you know how to still display data when there is an empty array within VSTACK? Currently my formula works but displays CALC when an array is empty. I have two tables, both with data filtered based on a particular month. The spreadsheet example works but needs maybe LET & ISERROR to ensure one empty array of results still displays the remainder. Can anyone advise please? =IFERROR(SORT(VSTACK(FILTER(FILTER(A3:E6,(D3:D6<=C9)*(E3:E6>=C9)),{1,1,0,1,0}),FILTER(FILTER(H3:L6,(K3:K6<=C9)*(L3:L6>=C9)),{1,1,0,1,0})),3,1,FALSE),"ERROR") Thank youMatt_PazFeb 18, 2025Copper Contributor78Views0likes3Comments
Resources
Tags
- excel42,225 Topics
- Formulas and Functions24,476 Topics
- Macros and VBA6,358 Topics
- office 3655,942 Topics
- Excel on Mac2,620 Topics
- BI & Data Analysis2,336 Topics
- Excel for web1,882 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,609 Topics