Gantt Chart without weekends
I have a Gantt chart in Excel to help us with project management and timing. I found an example a few weeks ago and have modified it to get about 95% of the way done. The issue is weekends. The chart, and the calculations, are using a full 7-day week and not a 5-day work week. Attached is a screen capture. L5 contains the formula that starts the chart. =Project_Start-WEEKDAY(Project_Start,1)+2+7*(Display_Week-1) Project_Start is the range T1:AC1, although I'm using it as the end/launch date of the project. My task calculations work back from that date. Display_Week is the range T1:AC2, again I'm working backwards from the launch date so that's a negative number here. As outlined in the red box, the tasks and chart are including weekends. How do I fix that so it calculates dates based on work week vs calendar week and also doesn't display weekends on the chart itself? Thanks!
Formula Needed
Using Alexis as an Example, I need Excel to search Column A for all the versions and then avg the data for the same Column found in B because sometimes there are multiple for the same Column. As in Column D. Then I would like the avg'd information to be put into H-J respectively for each name in Column G. Can this be done? I assume its going to involve MAP, Index? Where I am stuck is Column A includes the Column G but not exact match and the AVG data found in the Columns B-D and puts it in its corresponding column H-J.
Auto fill Department column
Good afternoon Microsoft Community, This is my first post ever with this site. I have the following project that I need help with. I need to auto fill the Department in column A in the Raw Data worksheet, with the DEPARTMENT name on worksheet Supervisor Drop-Down list. The Raw Data worksheet has Column F with the names of the current employees. I want a function/formula that will check for the names in the Supervisor's Drop-Down list and automatically add that department to Column A on the Raw Data worksheet. I have explore many different videos on the web and have not been able to find a solution. please advise on the best approach to handle this task.
Complex look up with output
Hi, I'm looking for help with a complex lookup problem. I have tried a number of options but haven't been able to achieve the desired result. If it were possible to use Conditional Formatting to highlight unique values incolumns from 2 different Excel sheets that updates when filters have been applied to each then this would be a simple solution, but unfortunately Conditional Formatting does not update/adapt to filtered data (that I can see), so I'm trying to achieve something more complex and dynamic with Ifs, AND, Lookups etc... Basically, a simplified version of the problem (which I have created a sample Excel sheet for this but there doesn't appear to be anywhere to upload a document) is: Lets say I have a list of training courses in a table with column headers for different Grade and within each Grade column there is L1,L2,L3... representing different levels. On another sheet I have a report of students who have competed various courses. I want to search the table by Grade an level for respective courses and search the students report to see if they have those courses complete. Any courses not listed in the student report, for that student, needs to then be displayed in a list so that it the courses can be assigned. The idea would be that multiple cross checks could be done for each student who is in a different Grade/level to see what they are missing. I created a 3rd tab with dropdown menus for the 3 variables, where the output list of courses should be displayed. Is that possible? Many thanks in advance for any help with this.
Excel functions for column compares
I'm new to Excel and want to find a function that compares one column to another and if the value goes up sends me an indication (trend up) and if the value goes down (trend down). For example Total Count Most Recent Today Count 15,054.29 10/30/24 18607.14 4.57 10/10/24 4.57 6,692.13 10/30/24 6889.51 3,504.65 10/30/24 4191.37 If Total Count is larger than Today Count - return trend down indicator If Total Count is less than Today Count - return trend up indicator If Counts are equal - no trend indicator Is there a function that can do this on Excel?
Set Default Search to By Columns in Excel
I have several Excel spreadsheets with over 100,000 lines. I would like to set the default Search parameter for each to search By Column instead of By Row. Preferably for all Excel documents, but I can live with this default for my most highly used large documents. There appears to be a way to do this in older versions of Excel, but the directions provided do not work with my current version of Excel. I am using Microsoft 365 apps for enterprise. Any guidance would be appreciated.XLOOKUP last non-empty cell in a table column
Hi all, This is my first time posting here. I'm trying to return the last non-empty value of a specific column of a table to another sheet but it keeps returning the error "This formula is missing a range reference or a defined name." The function I'm trying to use is: =XLOOKUP(TRUE,TblData[Running Total]<>"",TblData[Running Total],,,-1) where TblData[Running Total] is a column in the Table "TblData" on a separate sheet, "DATA". It works on the same sheet as the table so I'm wondering if it's something to do with specifying the sheet somehow? Any help would be greatly appreciated.
Excel data segregation
Hi All, I have orders data in Sheet 1, and it needs to be segregated into different sheets based on the segment in Column H (e.g., Consumer, Corporate, or Home Office). Further data analysis will be conducted in the individual sheets by adding additional columns. All data should appear in the respective individual sheets. Since the data in Sheet 1 is dynamic, any changes made there should automatically reflect in the respective sheets. What would be the best approach to achieve this without using a macro?
