Forum Widgets
Latest Discussions
Formula needed to retrieve % of correct reviews for an employee, where reviews are on separate tabs.
Hello everyone! Thank you in advance for any assistance you are able to offer. I'm working in MS 365 for web on a windows cloud environment through a Citrix network. Looking for a formula that will populate one employee's evaluation results as a % correct for each of multiple worksheet tables of reviewed items (pass/fail). See attached workbook for details on what I'm trying to do. I would like to be able to choose an employee's name from a drop down on the RESULTS tab, select the months to begin and end a date range and have this return the total reviews and number failed for each 'Review Name' for that employee. Thanks again! :)
Match/Index referencing incorrect row
I have a spreadsheet that I am working on in MS365. On sheet "A" I am drawing the following information: AGE (=G63) into cell S65 Weight Lifted (=F68) into cell S66 From sheet "B" (Reference data) Row 2 are the age categories (17 - 21, 22 - 26, 27 - 31, 32 - 36, 37 - 41, 42 - 46, 47 - 51, 52 - 56, 57 - 61, 62 Over. These are grouped as MDLAGE. Column A are the scores - 100 - 0 descending order. these are grouped as MDLPOINTS In cells B3:K49 are the weights lifted. The values vary by age and there are cells that are blank or have a "---" in them. These are grouped as MDL. The formula I am using is: Cell S67 - =MATCH(S65,--LEFT(MDLAGE,2),1) This formula is returning the correct column. Cell S68 - =MATCH(S66,INDEX(MDL,0,S67),1) This formula is referencing the INCORRECT row. It is returning row 40 instead of row 31 which is where the correct weight is listed. If I change the 1 to a 0 or -1 the return is N/A. I have tried multiple formulas to "skip" the blank or --- cells but nothing has worked. Any help would be greatly appreciated!
Conditional Formatting for multiple range
I wonder can we make conditional formatting in excel to hightlight certain values in multiple ranges in a couple of certain columns? A B C D E F G 1 value Percentages of Total value Percentages of Total value Percentages of Total 109 61 122 61 145 61 50 28 54 27 67 28 19 10 21 10 25 10 178 100 197 100 237 100 2 Values Percentage of Total Values Percentage of Total Values Percentage of Total 1 0 1 0 2 1 0 0 0 0 1 0 1 0 1 0 2 1 4 3 4 2 6 3 11 8 14 9 15 8 17 13 18 12 23 13 17 13 21 14 27 15 20 15 22 15 27 15 31 23 32 22 36 20 28 21 31 21 34 19 130 100 144 100 173 100 3 Values Percentage of Total Values Percentage of Total Values Percentage of Total 76 45 85 46 102 46 4 2 4 2 4 1 29 17 32 17 37 16 7 4 7 3,83 8 3 20 11 21 11 26 11 6 3 6 3 6 2 12 7 14 7 17 7 3 1 3 1 3 1 7 4 7 3 8 3 1 0 1 0 5 2 3 1 3 1 3 1 0 0 0 0 0 0 0 0 0 0 0 0 168 100 183 100 219 100 so for example all 3 tables in a worksheet, all tables had different row lengths and i want to hightlight automatically 3 largest percentages values in range between "Percentage of Total" and "100" which is the sum of percentages in each tables.so for Table 1 all of cells between "Percentage of Total" and "100" will be hightlight,for table 2 highlight values are 23 21 15 (1st column), 22 21 15 (2nd column), 20 19 15 (3rd column)and for table 3 it would be 45 17 11 (1st column), 46 17 11 (2nd column) and 46 16 11 (3rd column) Is there a way to do that?
Sum from cell across multiple sheet, depending on hire date
I can not figure this out, I am trying to make a summary sheet that will count the vacation days across multiple sheets from "Start" to "All" (these are timesheets between those named sheets) for employees. But I need the count to reset on their hire anniversary month and day. Employee 1 - Hired on 1/2/2001 (found in cell B5 on the sheet named "Summary") Employee 2 - Hired on 4/9/2020 (found in cell B6 on sheet named "Summary") In this example, any vacation days in cell F5 (employee 1) across multiple sheets between sheets named "Start" to "All" will count until the anniversary month and day arrives, then it will reset and start counting forward again until that date arrives again. Any help will be much appreciated!
Lookup seat number on table plan
Hello all, hope you can help me. In Excel, I've a table plan where I've assigned names to numbered seats. I now want to read the 'read' that table plan and create a list that tells me the name and seat number. I already have the list of names (in Column L), but need a formula for Column M to do the lookup of the seat number on the table plan. The seat number is always located in the cell above the name. For cell M2, the formula should return that Arthur is in seat C7, and in M3, that Ben is in seat B3, and so on. I suspect the formula could be a combination of one of the Lookup variations, INDEX or MATCH, or even using the CELL command, but I've not fathomed it yet. Does anyone have any suggestions on how I could do it? Many thanks
