Move repeating columns into rows
Hello guys, I have a set of data that looks like this: Name Hours Date Hours Date Hours Date John 3 1-Jan 4 5-Jan Ann 4 4-Jan 2 8-Jan 2 9-Jan Each Hours data cell have a comment in it, and I'm trying to turn it into something like this: Name Hours Date John 3 1-Jan John 4 5-Jan Ann 4 4-Jan Ann 2 8-Jan Ann 2 9-Jan Is there a way for me to do that while retaining all the comments in each Hours data cell? I'm using Excel 2016. Best Regards, John
Calculate hours using pivot table
Hi, Im making a personell planning sheet and I want to calculute the hours each teacher is teaching using a pivot table. My data is formatted in 2 tables like this (simplified): Lesson Hours Teacher 1 Teacher 2 Lesson 1 2 Paul Lesson 2 3 Pete Lesson 3 2 Paul Pete Teacher Max working hours Paul 10 Pete 15 Now its easy to create an overview of how many hours each teacher is teaching with one teacher collumn. But it need to calculate the hours based on 2 collumns like this: Paul -> Lesson 1 + Lesson 3 = 4 hours Pete -> Lesson 2 + Lesson 3 = 5 hours Then the next step is to use a metric or KPI to calculate if the teacher is exceeding their max working hours. I hope someone can help me out with this... Thanks!
Not Allowing Entries to be blank
I have a cell that is formatted for a date that will get updated by the user (F4 Below). And then column A is just copying that value all the way down. I want to prevent the information in column A from being deleted accidentally. It will likely be a hidden column, so accidentally deleting it is possible. I tried data validation and rules, and they worked in all cases except if they are deleted. How can I prevent A1 from being deleted, but still have the user be able to update the date in F4 and have A1 update.Calculating hours using pivot table
Hi, I'm making a personell planning sheet and I want to calculate the sum of hours teachers give lessons. I have 2 tables and my data is formatted like this (simplified) Lesson name Hours Teacher 1 Teacher 2 Lesson 1 2 Paul Lesson 2 3 Pete Lesson 3 2 Paul Pete Teacher name Max working hours Paul 10 Pete 15 Now I want to create an overview of how many hours each teacher is teaching using a pivot table. This is easy when there is just one Teachter collumn but I need to calculate the sum using both teacher collumns... I need to overview to be something like this: Paul -> Lesson 1 + Lesson 3 = 4 hours Pete -> Lesson 2 + lessen 3 = 5 hours Then the next step is to use a metric or KPI to calculate if each teacher is exceeding their max working hours... Can anybody hlelp me with these problems? Thanks!
Gantt Chart Weekday Function
I am trying to use a gantt chart with conditional formatting for a project. I have my conditional formatting set up the following: =AND($D9<=H$5, WORKDAY($D9-1, $C9)>=H$5) problem is, some of the tasks have a duration of less than 1 day (column C) and so adding the -1 is blanking out several tasks. How do i correct this? I'm just starting to learn conditional formatting so some of this is still like a foreign language to me. Thanks!Chart linking with Name Manager
so it's known chart behave different than other items, i linked it to a box that indirect the limit of which it should take values from a column, like i used this code to do it: =OFFSET('Study '!$F$25,1,0,'Study '!$BD$26), but this fails when i want it to take starting and ending limit with offset command, it just re input values, like if i want beginning to be 100 and ending to be 300 it reads first 100 and then reread them plus the extra 200 so i have 400 values. is it possible to make it so from column F it starts taking values from lets say box BC and the ending limit to be from BD? i tried looking it up and figuring it but you know how it goes with chartsHow to make a chain selection with drop-down lists
What formulas can be used to generate a chain of filters from a selection of data that, in turn, filters the data again until a specific element is selected? For example: I have lists of various pieces of equipment categorized by type, brand, and model, and what I need is to select them in stages. That is, I position myself in a cell that displays a series of data from the "type" matrix (a long list located on another sheet). When I select a piece of data, the next cell only shows me the list of data, but also the previous cell from a list on another sheet. And in turn, the next cell shows or displays the list from the selected cell. It's a chain filter where each selected piece of data depends on the previous one.
name manager rejecting lambdas
I tried pasting a lambda in name manager but excel refused. I belive that it's because either it is too long (which it isnt) or it has too many optional parameters (8). Anyone knows why name manager will reject to paste my lambda? These are the parameters: =LAMBDA(array,slice1,[index1],[return_index_slice2],[index2],[return_index],[if_not_found],[logic],[headers],[function],LET(...))
