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.
People data lookups
Hi, I have created an MS Form where anyone can nominate their peers for recognition - we have 20k employees. I've added a field in the form for a unique employee number for the nominator to include. So they would submit the nominee's: emp number, full name, role, area of the business. The output comes to me in excel and I need to update a list of 00's of employees, where they work, their contact details, line manager detail etc. The challenge is this: whilst I can do all the look-ups to the external data to provide this, the output of the form could have numerous errors. First of all, a missing emp number or incorrect emp no. Second of all, incorrect name spelling, or shortened names, or duplicate names, or too many spaces etc. Then there's incorrect role or missing role, and finally the same for area of the business. Using either multiple IFs or look-ups or Power Query, what's the best solution to pull through the correct emp number (from my external data), when those first four fields could be all wrong, partly wrong, missing or all correct, and what about dealing with duplicate names? I can concatenate if the four fields are correct, but this isn't reliable. Is there a fuzzy match that takes the best guess at the four fields completed - leaving me to just check a handful of #N/As? I have Copilot and licence too. Thanks!How do you respond to...
Hello my Excellers, I wish you all a happy new year full of great things. I have a question and I am not sure if this is a purely an Excel question or something else but maybe I start here and see how it goes: As you all may have noticed the AI craze is blooming like crazy. If you want to sell lettuce slap AI on its label and step back. I use Bing to search for VBA examples. For example I typed "excel vba code to find cells with formulas" and I get AI to find or "compose" a decent example that with minimal editing will work perfectly. But then at the end of the code and its explanation I sometimes find these kind of questions: "If you want to list the addresses of formula cells instead of highlighting them, I can give you a version that outputs them in the Immediate Window or a new sheet. Do you want me to provide that listing version as well?" How do you say YES I want that code that would list the addresses in a sheet? I see buttons like "Undo" rounded Left Arrow, or "Redo" rounded Right arrow, and a "Copy, "Export", and then "Tweak Content", "Rewrite" button, A "Testing Tools" dropdown with options like "Generate test data" and "Integrate test cases", and another dropdown called "More Actions" with options like "Perform code review", "Explain the code", "Add error handling", and "Make code compliable" and the last dropdown "Work Report" with its own options. But I do not see anything that would allow me to say YES I want that code that would list the addresses in a sheet? Thanks for any insight... GiGi
Remove "Ghost" Excel sheet object from VBA
Hello, I am unable to remove these "Ghost" excel objects left behind after deleting the sheet. I have tried multiple methods that I found on the internet to remove these objects, but nothing works. The sheets are not hidden. The sheets do not existing. That is the "hard coded" name created when the file is created from copying the "CAD" or "CAD_Template". The "remove" button is grayed out. I tried basically everything CoPilot could tell me to remove this. This is what it looks like below. When I run the program, the program creates "CAD4" as the next hard coded name.
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,
Chat with Copilot popup on every Excel launch
An ad for Copilot shows up every single time I open Excel. Clicking Not Now closes it, only for it to reopen again when I open Excel later. Is there any way to disable this or remember my selection each time? It is frustrating to have to deal with it all day long. I do not have a Copilot tab in File > Options. Optional connected experiences is already disabled. Microsoft® Excel® for Microsoft 365 MSO (Version 2511 Build 16.0.19426.20218) 64-bit Windows 11 Pro 25H2Formula/method to link the data/responses I get from my forms into a different table.
Hi everyone! I need help with a project that I'm creating. Im making use of Microsoft Forms in order to input certain information. I made use of branching since some answers depend on the previous choice. So moving on to my problem, I want my table2 to get the responses/data from the data table created by the forms. Table 2 Table of responses For reference for the spill error, here is the formula that I used. Any insights will help a lot. Thank you have a great day everyone.Data Reconciliation Assistance Needed – Time Range & Sum Matching
I have two sets of data that need to be reconciled. Specifically, I’m trying to identify which combinations of numbers from these datasets can sum up to a specific target value. Additionally, the reconciliation should only consider entries that fall within a defined time range—from approximately 8:00 AM on one day to 10:00 PM the following day. I’ve tried using Solver and Goal Seek, but the results don’t seem accurate. I also attempted using Microsoft Copilot, but the outcome still appears incorrect. Could you assist with identifying or generating the correct combinations based on the criteria above?Sheet View Issues with Excel Web Browser
Hi, I have created a Excel document for my department to use, I tried Excel App but due to the size of the Spreadsheet it kept freezing, as a result I switched it to use Excel through Web Browser. The sheet no longer freezes which is good. I do have another issue though.....I wanted multiple users to be able to access, edit the sheet at the same time so I did some research and Sheet View seemed to be the way forward. I created a Sheet view for each employee to use so when you go to View, Sheet View and click on the relevant person their work is updated, saved etc and this shouldn't affect others view. This is working in terms of people editing. The main issue now though is that despite following advice sometimes when someone changes a filter on their own sheet view it seems to change others view. My understanding was you can hide, filter etc within your own Sheet view but this doesn't seem to be the case.......PLEASE HELP!!!! My excel and computer Skills aren't excellent to please any simple advise would be great.
