Forum Widgets
Latest Discussions
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!
Need help with a formula
Im building an employee data sheet and one of the columns is HIRE DATE, the next column is 90 DAY REVIEW DATE. Both columns are date formatted. The formula in the 90 DAY REVIEW DATE cells is simply the hire date + 90. The problem is that if there is not a date in the hire date cell, the date that is being displayed in the 90 DAY REVIEW DATE is 3/30/1900, (I assume this is the default beginning date in Excel?) I do not want anything displayed in the 90 DATE REVIEW DATE column is there is not a date in the HIRE DATE column. Can someone help me with this please?
#unknown! displayed in excel where an inserted picture should be located
A supplier is sending me an excel template which contains pictures in a cell The picture can be viewed in web based outlook and web based excel but when I download the file the cell states #unknown! I believe the supplier is using a personal 365 account and I am using 365 Apps for enterprise I think they are adding the picture by completing the below (taken from MS site) this is what I see when I open it up on web based outlook But this is what I see when I download an open in excel desktop Any help is greatly appreciated
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! :)Full Dark Mode in Excel
Currently you can set Office Theme to Black which acts as a dark mode. In Word, setting this to Black makes the page dark and the text white when it is set to “Automatic”. I was wondering if anyone had an idea if Microsoft is planning to release this same thing for Excel too? Right now Excel when using the dark theme makes everything dark except the cells. Which kind of makes no sense to me. This is where your eyes are most of the time. It needs to be a proper fully dark theme. Is there a way to get a full dark mode without manually setting the background color of the cells dark and the text white? Can a plugin be made to do that while keeping the actual cell background “empty” and the text “Automatic”? Is there already a plugin that does that? Thanks!
Conditional formatting not reading dates
Good morning, I have a spreadsheet in which dates have been formatted as dates in the Home tab, and where I already tried using "Text to Columns" in the Data tab to make sure the dates are formatted as dates and not text. Nevertheless, when I try to apply conditional formatting to highlight dates that are less than TODAY(), it doesn't work as intended - it just highlights everything. If the data is formatted correctly - as dates - what else could be going wrong? Or is the data still not formatted correctly?
