Forum Widgets
Latest Discussions
How 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. :)
Power 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.
Excel 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.
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-MarieDealing 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 you
Auto-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 };"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.
Table: Formula to return a value from the table if other cells contain certain values
Hi everyone! I have a table (like in the screenshot below) that contains certain values in column D/E/F/G. I want to create a formula in which you can enter in the below B2/B3/B4 values from columns D, E and F and depending on the row in the table where all 3 values are true, then B5 returns the corresponding value from column G in that row. Does anyone have any idea which formulas I could use for this? Thank you so much for your ideas! 🙂
