Help needed with IF and COUNTIFS Formulas
Is anyone able to advise the following formula: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & V3,$D5:$D15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & V3,$H5:$H15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & V3,$L5:$L15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & V3,$P5:$P15, ">" & V3) Is there a way to simplify this? Is there a way to make this more accurate? Cells in column G & H, I & J, O & P are using the following format: =IF(C6="","",C6+E6) Cells in U4:CC4 are using the following format: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & U3,$D5:$D15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & U3,$H5:$H15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & U3,$L5:$L15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & U3,$P5:$P15, ">" & U3) Cells in U5:CC15 are using the following format: =IF(U$4>=$T5,1,"") My issue is is when I put in the three break times, the mid break comes out at a shorter time. My other issue is is that when I put in the times in row 5,6and 11, the data is coming up as a combined data in rows 5, 6 and seven on the page two. Just for reference, "page two" is the same spreadsheet. What I need to happen is that I enter in the shift start time and finish time. This then populates through to Break 1, 2 and 3. The Time entry is the time the break starts. ie: 1 hour after start of shift, 1 hour after coming back from break, etc. The break entry is the duration of the break taken. ie: 30 minutes. Once all the info is put in, the relevant "Time Block" on "Page 2" shows a 1. What is happening at the moment is that when I enter all the time data, the time blocks are not populating correctly in accordance to the entry. Basically, If I have numerous people on shiftI need the time blocks to show where I have shortfalls in shift cover and not having too many people on break at the same time. IE: Link to Live Copy: https://www.dropbox.com/scl/fi/eur1j526htu1j8a4d4290/Staff-Breaks.xlsx?rlkey=r4tm9xts4tonofpa2th2cusfw&st=nueyk0d7&dl=0 Any ideas would be greatly appreciated.
Cell drop list of workbook sheets
Hello Is there a way to have a drop list of all the sheets in a workbook in selected cells and the selected sheet from the list, populates other cells in the first sheet? I want to easily see what machines are connected to a list of RJ45 wall points. In the workbook, there is 1 sheet that has a list of wall point and each machine has its own sheet. Each machine sheet has lots of corresponding data that is not pertinent to the RJ45 wall point sheet. My goal is to select a cell next to a given wall point row in the RJ45 wall point sheet, that drops down a list of machines (that are all the machine sheets in the workbook). The dropdown list selection will populate pre-defined adjacent cells with corresponding data from the selected machine sheet. Each cell in the MACHINE NAME column has a drop down list of the sheet names to select from. Once a machine is selected, the hardware names and model columns that are on the same row are populated with the corresponding data on the machine sheet.Excel sheets that populate a drop down menu
Hello Is there a way to have a drop-down list of all the sheets in a workbook in a selected cell and the selected sheet from the list's data populates other cells in the first sheet? My goal : To easily see what machines are connected to a list of RJ45 wall points. In the workbook, there is 1 sheet that has a list of wall points . Each machine has its own sheet. Each machine sheet has lots of corresponding data that is not pertinent to the RJ45 wall point sheet. My goal is to select a cell next to a given wall point row in the RJ45 wall point sheet, that drops down a list of machines (that are all the machine sheets in the workbook). The selected dropdown list machine, will populate pre-defined adjacent cells with corresponding data from the selected machine sheet. Each cell in the MACHINE NAME column has a drop down list of the sheet names to select from. Once a machine is selected, the hardware names and model columns that are on the same row are populated with the corresponding data on the machine sheet. I have asked ChatGTP and Gemini AI to help ... without any success. Is this possible to do? Thank youHelp with Excel Sorting
Hello! I am looking for help with sorting data on a sheet with form responses to different excel sheets. I want to filter by column G to different sheets ("Near Miss", "Adverse Event", "Sentinel Event". The data automatically goes to sheet1 via form responses, but I am trying to streamline the data into specific types of events. I'm using office 365. TIA!
Conditional formatting based on a checkbox
Good afternoon. I wonder if someone can help me please :-) I have a spreadsheet that has rows of client data, what I am trying to achieve is when I click the checkbox it turns the row green. I have researched it and all the info look different to the conditional formatting I can see, I am using Excel 365 web based excel I don't see the the option 'Use a formula to determine which cells to format.' I see apply to: and Format Cell where the formula is true can someone please help Thanks Joel
Moving a column of text data into 3 columns of data?
I have a column of text data cells 1,2,3,4,5,6,7,8,9 and longer. I want to create 3 column of data to graph and manipulate Cell in Columns. 1,2,3 3,4,5 5,6,7 8,9,10 and longer. So i need to create 3 columns of data from 1 column of data. I am using Mac Excel 16 and I can not make this happen. I have tried all sorts of solutions. Help? Thank you,
Disable Auto-Fit/Auto-Wrap on Web?
About a week ago Excel rolled out a new feature to me that offered to auto-fit my cells whenever needed. Not knowing what it was, I accepted it and turned it on. Now, I want to turn it off and cannot find it anywhere. I've tried following https://support.microsoft.com/en-us/office/undo-automatic-formatting-in-excel-54eba206-110c-445a-89f1-c4eb67a36bd4?showContactUsNav=False&ns=EXCEL&version=90instructions, but these instructions are for the app, not web, and the issue doesn't replicate on the app. Below in E46 is an example of the issue. I want to paste links without them auto-wrapping my text or auto-fitting the column. I have tried turning wrapping on and off again in this column, but it doesn't work. I could manually disable the wrapping every time I enter a link, but I would much rather just turn this feature off.
Unprotect in Office Scripts
I am writing an office script that requires sorting on a sheet with the overall sheet protected and specific cells not protected. I need to be able to sort an unprotected range using an automation but the protection is restricting it even though I have "sort" allowed in the protection options. Is there a way to unprotect and then re-protect the sheet as part of the scripts? Including passwords? function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Clear auto filter on selectedSheet selectedSheet.getAutoFilter().clearCriteria(); // Custom sort on range range C9:I6001 on selectedSheet selectedSheet.getRange("C9:I6001").getSort().apply([{key: 0, ascending: true}], false, true, ExcelScript.SortOrientation.rows); // Apply values filter on selectedSheet selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 6, { filterOn: ExcelScript.FilterOn.values, values: ["1"] }); }
