Search results

Hi All, I have been trying to use Excel Solver to write what should be a simple solution but I do need assistance to get me started. My Goal is - create enough 28 day roster profiles (with work days and days off) that meet the following rules. Each roster line must: contain 8 days off in 4...

I am trying to write a formula from the data in columns A to E which finds the maximum "Date To" where a "Staff" is the same and "Activity Code" is the same in a consecutive run of days in "Date To" column, then place answer into the "End" column. I have provide the expected output in the "End"...

I have been using the =Filter formula in Office 365 which is great but I am stuck on whether it is possible to display non contiguous columns. For example, I have a table and need to display column A and C in the output, not column B. Normally I can enter a range like...

Hi, I have partially solved a problem by finding the nearest value in a range (A2:A28) of another value (in cell C1) by using the following formula: {=INDEX($A$2:$A$28,MATCH(MIN(ABS($A$2:$A$28-$C$1)),ABS($A$2:$A$28-$C$1),0))} The part I am having trouble with to find the nearest value in a...

Hi, I am hoping someone can assist me in improving my formula (I think I need an array formula but am stuck on writing it). In the attachment I have a list of staff who request to move from their city to a choice of another 2 cities. My objective is increase my staff requirement by 3 in BNE...

Hi, can some one please assist with showing me how 2 array formulas can help solve 2 problems as in reference to the attached file and info below: Problem 1 For each person in column A, I am trying to count the number of consecutive "Y" in column C. I want to show the count value in column D...

Hi, I am trying to rank some data in column A from highest value to lowest value. Each rank value must be unique. I have sorted the data by col A descending. Some of the values in column A are the same, but most are different. in all cases the unique rank formula works perfectly, except for the...

Hi, I have read various posts on this problem but haven't found a solution so I am requesting your help please. I have 4 columns of data in one worksheet (Sheet1) Col A = ID Col B = Date From Col C = Date To Col D = Workgroup I another worksheet (Sheet2) I have Col A = ID Col B = Date Col C =...

Hi, I would appreciate some help with what should be a simple macro for some people. In the attachment I have 2 worksheets, named "Was", "Now". In Was you will find a staff number and all their associated dates to the right. What I need to do is transpose all the dates (ignoring all the blank...

Hi, does anyone know a way that I can share a excel online spreadsheet that is shared with many people, but I only want each person to have access to their own range within a worksheet. E.G. person A can edit rows 2:5, person b can edit rows 6:9 etc. Alternatively, if the above is not possible...

Hi, attached is a file that contains 2 worksheets (Qualification, Tasks) along with a 1 pivot table. The "Tasks" worksheet contains daily activities of many staff in Nov2016. Each staff member is associated to a workgroup. The "Qualification" worksheet contains the historial and future...

Hi I have a worksheet that gets data from an external source and populates a worksheet as a table. Each time I extract my data from the external source the number of rows change. Next to the table I have 2 columns that contain formulas which reference the table data. What I am trying to do...

Hi, I have tried various array formulas and keep getting stuck. I would appreciate some help with the following. In cell B2 is where I want to put my answer, by looking in A2 within a string of text that matches something in the range from $D$2:$D$4, then find the corresponding information...

Hi, the following attachment on sheet2, column C is calculating the number of times a number falls within the range in column A & B as referenced from sheet1, Column B. What I want to do is add the following conditions the current formula in sheet2 (highlighted in green)... - If sheet2, A2...

Hi, in my attached workbook I have a some conditional formatting in every second column. The conditional formatting is checking if each cell is a greater value than the one to the right of itself. If TRUE then highlight green. In cell CW2 I want to see if there is a shorter/smarter way of...

Hi, I have written this formula and want to make it more efficient by replacing the OFFSET with INDEX to make it less volatile. {=MIN(OFFSET(A2,,1,INDEX(MATCH(1,--ISNUMBER(A2:A300),0),)))} What this formula is doing is finding the first cell below A2 to A300 that contains a value then once...

Hi, for some reason I can't get this simple MIN IF & MAX IF formula to work. The MIN IF formula I have attempted is to look for all dates in Column A that are between 2 dates then return the min value from column B. I have also done the same for the MAX IF. See attached in cell G3 and H3. Can...

Hi, I am having trouble with my chart line scaling correctly. I have 2 variables to control what want to see on my chart... 1. "Start From # Rows Down" - which controls my starting point (ie my row I want to start from) 2. "Data Points" - which controls how many rows of data I want to display...

Hi, in the attached spreadsheet I have have array formula that searches for multiple values in column C that exist between 2 values in column B. This part of the formula works fine. The problem I am now trying to solve is ... When my formula finds more than 1 value in column C that exist...

Hi, in the attached file I have data in column B and C that I want to evaluate in columns D,E,F,G. I have manually entered four very simple formulas to evaluate the data but it is not a simple copy down to get the results I need, as you will see in the file if you F2 the formulas. I want to...

Hi, I want to use an INDEX formula (non volatile) instead of an OFFSET formula as their are thousands of OFFSET functions within my spreadsheet. This formula is wrapped inside other calculations so I only need help with an alternative to this. My current OFFSET formula looks like this...

Hi, I am using the following formula (in Excel 2010) to test if a condition is TRUE or FALSE then summing the result from the previous cell above as I copy down each row (210,000 rows x 12 columns). All named ranges are single cell references except qRange, qDateDFC which 6000 rows x 1 column...

Hi, I am trying to chart the following data (without using a pivot chart, although I am showing a pivot chart below for demonstration) and I am having problems because of the "rsSeries" column. I want the X axis to be rsDate, rsAmount to be plotted by rsSeries and rsDate. Is this possible...

Hi, I need some assistance with creating a formula to filter a list. In the attached workbook I have unfiltered data in columns A:F in columns I:K is my search criteria in columns N:S are the results of the search The search criteria requires matching the "Series" from column I & "Level" from...

Hi, I have a problem which I can't solve efficiently (but I can solve in some clunky steps). I am asking for your help. The file attached contains all the data and also a copy of the instructions of what I am trying to achieve. Below is a copy of what I am trying to resolve but it won't make too...