Posts by aspasovski86

    [SIZE=13px]Hi,[/SIZE]


    [SIZE=13px]I have the following problem with drop-down lists dependent on each other (second on first and the third on the second and the first one... )[/SIZE]


    [SIZE=13px]Namely, the first drop-down list have no issue and I am able to choose from the list, the second one, which is dependent on the first one, is also working and I am able to choose from the list depending on the first choice, but when I get to the third drop-down list, I am able to see the in-cell arrow, but nothing happens... I need to go to the data tab, open data validation, (the formula is still there for the cell(s) ), click ok and it activates the drop down list and I am able to make a choice of the list, depending on the previous two choices... [/SIZE]


    [SIZE=13px]This happens in both, Excel 2013 and Excel 2016, the file is saved as *.xlsm. I also deleted all macros I had, saved it as *.xlsx, unfreeze all panes, unlocked all cells in the second worksheet where all data is placed. Unprotected all worksheets... and again I save the file, close it, reopen it and the same problem with the third drop down list appears...![/SIZE]


    [SIZE=13px]This are the formulas that I am using for the dependent drop down lists[/SIZE]


    [SIZE=13px]Data Validation formula in the second drop-down column: =OFFSET(Data!$W$2;MATCH($F7;INDIRECT(Table1[FirstColumn]);0);1;COUNTIF(INDIRECT(Table1[FirstColumn]);$F7);1)[/SIZE]


    [SIZE=13px]Data Validation formula in the third drop-down column that is actually not working after reopening the document: =OFFSET(Data!$Z$2;MATCH(1;(INDIRECT(Table2[FirstColumn])=$F7)*(INDIRECT(Table2[SecondColumn])=$G7);0);2;COUNTIFS(INDIRECT(Table2[FirstColumn]);$F7;INDIRECT(Table2[SecondColumn]);$G7);1)[/SIZE]



    Search all over the internet for a similar solution, but couldn't find appropriate one....
    Would be very grateful if someone shares a solution


    [SIZE=13px]Thanks in advance[/SIZE]

    [USER="31712"]Carim[/USER] I think we can mark this thread as {SOLVED} ( https://www.ozgrid.com/forum/f…lue?p=1207546#post1207546 ) as this is what I've been looking for... (Cannot find option to mark this thread as solved, neither an option to edit the first post and add "solved") :wowee: :party:


    Thank you very much for all you work, you are incredible!!! :thanx: :yourock:


    Hopefully, this will be a solution to someone's else problems in future :smile:


    Cheers! :drink:

    Hi Carim


    All my remarks are into the workbook attachment.


    The "problem" that I found regarding the calculation of workdays is in worksheet {test 3}, In

    [SIZE=13px]worksheet {test 4} after longer review of the formula I thing I managed to solve it, but please take a review on it, and confirm that you have the solution for this post? :wowee:


    In worksheet {test} there is also small remark, it is explained inside. Let me know if you agree with it....[/SIZE]


    Million thanks for you time as well as sharing you knowledge and expertise!!! You are awesome!

    [SIZE=13px]:pirate:[/SIZE]

    Hi,


    Regarding the 31 days (or whole month) of illness, will the following add to the formula work in C17?
    {=IF($C17="";"";INDEX(IF(SUM(FREQUENCY(IF(($C$3:$AG$3="IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3));IF(($C$3:$AG$3<>"IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3))))=DAYS((EOMONTH($AI$2;0));$C$2)+1;SUM(FREQUENCY(IF(($C$3:$AG$3="IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3));IF(($C$3:$AG$3<>"IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3))));FREQUENCY(IF(($C$3:$AG$3="IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3));IF(($C$3:$AG$3<>"IL")*(WEEKDAY($C$2:$AG$2;2)<=7);COLUMN($C$3:$AG$3))));SMALL…..


    :smile:

    Hi,


    Thanks a lot for the attachment


    I was testing it today, and found two "bugs"... I re-attached the file with my remarks (test 1 worksheet for the first remark and test 2 worksheet for the second remark).


    Namely, half of the day I was trying to find a solutions.... And I thing I found for the first thing (related to the final hours of illness.... if you have two identical periods of illness, with the index/match function, you are getting only the firs row with the values, the others are omitted). Please check if it is ok? :)


    Second one is explained in the worksheet (test 2)


    I will continue to test it, and make a deeper review as I can understand what are all functions and formulas doing :D


    [USER="31712"]Carim[/USER]

    [SIZE=13px] [/SIZE]

    ,thank you very much for your time and effort. I think we have 99,99% of the solution

    Hi,


    Indeed an interesting problem, as I was searching all over internet to find something similar, but always ended up in solutions that determine the unique values, the frequency, max consecutive values and so on.... but none of them splitting the same values into consecutive sets of values, execute them separately from the array and count them by a condition or two as I need... :hubbahub:
    The formulas in cells B10, B11 and B12 are actually checking if the consecutive days are smaller or equal to 7, between 8 and 30 or larger than 31, accordingly. I am not 100% sure if the formulas are correct, but the point is that they are checking if the first day of the month is illness, If so, it is logically that the illness period is continuing from the last month (which does not have to be of course....), afterwards it checks the condition if value in B3 plus the first set of consecutive values of the array is smaller, between or larger, if true it sum cell B3 and the first set of consecutive values of the array plus all values of the array with the condition (<7 8-30, >30), minus the first value of the array because it is already calculated (I figured out that I have made some omissions in the formula, attached is the updated formulas for that cells). The false values are checking only the conditions and sum the values in cells accordingly.
    Row 4 and 5 are actually the same thing with that difference that I needed only the highest, end, values of the consecutive sets of the array... If I execute this formula into the fourth row it would calculate everything smaller then 7 (1, 2 3, 4 ,5 etc...)
    If it is not possible to have a solution with only one formula in cell, I dont mind using this type, because everything will be working in background in separated sheet, I will read only the final values from C10 to C12 in another sheet (or workbook).... and here I really don't have an idea how to make a formula to put values from row 7 as they are put from row 5 into C10 to C12 accordingly.... :crying:

    Hi Carim,


    Solution without rows 4,5,6,7 would be the perfect one... :grin:
    The first illness period falls into third category because in the previous month that person had illness for 25 consecutive days (Cell B3), and it continued into this, current, month. That way it is a 33 consecutive days of illness which fall to the third category (more than 30 days of illness). This category of illness is covered by the health insurance. So the employee will get 100% payment for those hours from the health insurance. The second period is six consecutive days (5 without weekends) and those 40 hours are paid 70% by the company as a category for up to seven consecutive days.
    The complicated thing here (in our country) is that if you want to determinate the category of illness you are taking in consideration the consecutive days of absence, but among those sets/periods you are calculating only the working days as hours (regular 8 hour workday) that should be paid (either from the company or the health insurance institution).


    BTW I used rows 4,5 6,7 because I am not an excel expert, so that way, in order to get a solution, I need two or three more steps to get it :D


    Hope this was helpful!


    Cheers

    Hi,
    I dont know if the topic represents the real thing I want, so here is an explanation...I have these entries in my worksheet:
    [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    Shift table

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [td]

    Th

    [/td]


    [td]

    F

    [/td]


    [td]

    Sa

    [/td]


    [td]

    Su

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [td]

    Th

    [/td]


    [td]

    F

    [/td]


    [td]

    Sa

    [/td]


    [td]

    Su

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [td]

    Th

    [/td]


    [td]

    F

    [/td]


    [td]

    Sa

    [/td]


    [td]

    Su

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [td]

    Th

    [/td]


    [td]

    F

    [/td]


    [td]

    Sa

    [/td]


    [td]

    Su

    [/td]


    [td]

    M

    [/td]


    [td]

    Tu

    [/td]


    [td]

    W

    [/td]


    [/tr]


    [tr]


    [td]

    Consecutive days of Illness form previous month

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    3

    [/td]


    [td]

    4

    [/td]


    [td]

    5

    [/td]


    [td]

    6

    [/td]


    [td]

    7

    [/td]


    [td]

    8

    [/td]


    [td]

    9

    [/td]


    [td]

    10

    [/td]


    [td]

    11

    [/td]


    [td]

    12

    [/td]


    [td]

    13

    [/td]


    [td]

    14

    [/td]


    [td]

    15

    [/td]


    [td]

    16

    [/td]


    [td]

    17

    [/td]


    [td]

    18

    [/td]


    [td]

    19

    [/td]


    [td]

    20

    [/td]


    [td]

    21

    [/td]


    [td]

    22

    [/td]


    [td]

    23

    [/td]


    [td]

    24

    [/td]


    [td]

    25

    [/td]


    [td]

    26

    [/td]


    [td]

    27

    [/td]


    [td]

    28

    [/td]


    [td]

    29

    [/td]


    [td]

    30

    [/td]


    [td]

    31

    [/td]


    [/tr]


    [tr]


    [td]

    25

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    IL

    [/td]


    [td]

    1

    [/td]


    [td]

    2

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [/TABLE]

    This is a shift table from one employee... ,The upper rows are the days of the month, and down is the row that represents the shifts. Among the regular shifts the employee might be absent because of an illness. The first column represents the days of illness from the previous month (manually entered, if he had any). and with IL are presented the days of illness for the current month. What I want to do in the following table: [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    <=7

    [/td]


    [td]

    40 (expected value from the shift table)

    [/td]


    [/tr]


    [tr]


    [td]

    8-30

    [/td]


    [td]

    0

    [SIZE=13px](expected value from the shift table)[/SIZE]

    [/td]


    [/tr]


    [tr]


    [td]

    31>=

    [/td]


    [td]

    48

    [SIZE=13px](expected value from the shift table)[/SIZE]

    [/td]


    [/tr]


    [/TABLE]

    Is to count the different periods of illness. check if every of them are less then 7, between 8 and 30 or 31+ consecutive days, multiply by 8 (as a regular working day) and place them into the right column respectively. When multiplying as a regular working days it should count and multiply only if it is a workday, but not weekends (Saturday and Sunday). This is necessary because different period of illness is paid differently (from the company, from the health insurance etc.)
    I tried to do something by using Frequency function, but I stuck, searched thru internet for a solution, and could find any,,,,
    Hope someone has it....
    Thanks in advance


    Regards
    Aleksandar