Search results

Thanks @p45cal I will try this and see how it goes. Cheers GB

Hi @p45cal thanks for your help, much appreciated. In relation to Point 5 above (sorry not clear) - The "W" days must not be less than the "Daily Work Requirement", therefore if too many "O" days are given on a particular day then there is not enough staff to cover the work (W). I opened the...

Hi All, reaching out again to see if anyone can help with the problem above. Any solver experts amongst the group? Regards GB

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...

Hi Bosco, this is great. Thanks so much. regards GB

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"...

Thanks for your help Chihiro. I antipated this could be solved in one formula. I can work with your suggestions. Cheers GB

Following on from your help, I have a further problem which I have attempted to solve but can't find the correct method. I am trying to filter a table where I want to find all dates >G1 and return these dates with data from another column that has a table header name = F1. You will some...

Hi @Peter Bartholomew & @Chihiro much appreciated. Regards GB

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 p45cal, also appreciate you taking the time to respond. Thank you regards GB

Hi Peter, appreciate your response. I haven't tried xlookup yet but you have given me something to work on. Thank you. Regards GB

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 Narayan thanks for a great solution. I will test this thoroughly. Sorry for the slow reply. Regards GB

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 Narayan, thanks for your reply, but you will notice for example there are quite a few duplicates in the data, (e.g. A231 & A232 are the same value) yet the rank calculation worked in all cases but the one I highlighted as the problem. Hi Bosco, that is useful to know, but definitely not...

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 Narayan, the formula is perfect and simple to understand. Most appreciated. cheers GB

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...

Thanks for your input. I thought as much. I will try PowerQuery or similar. regards GB

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...