Posts by tr1face

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Index help needed

    Hello Oz,


    I would like to improve a calendar and I'm attaching the workbook for your reference.

    As it is now it's using an index formula to retrieve a maximum of three daily events (c-f per colors) and arranged based of priority in the Mv sheet. Everything it's working as supposed, but I would like to have a start date & end date for those events while it should populate the period (excluding weekends) accordingly, because now it works with the "End Date" and if I need to add the same events in my calendar for a longer period (let's say 3 weeks) I would need to have 15 rows added, which is not practical.


    Another request would be for a formula to add the corresponding information in the newly created yw sheet, but just at a month level, having just the "Calendar entry" text and the date period


    Let me know if I can be of any help!

  • Auto move to next cell

    Hey Hamp,


    Until you're pressing Enter you're still gonna be in Edit Mode, so I don't think it's possible.


    You could use another cell on your sheet to enter one long number which consists of all your data, then split it up into single digits, listing them in desired rows

  • VBA help needed

    Found some useful code on GitHub, which was made Public and I am now closer than I was before.


    Below will extract the number of required rows (random) for each person, but there are numbers required for each process as described in "Parameters" tab. Can the below code be modified to extract accordingly?

  • VBA help needed

    Hi Experts,


    I would like to request your help for a vba code which should randomly pick rows from designed worksheet and add them to a new sheet (or individual sheets if filtering it's an option). I am attaching a workbook with dummy data, hopefully someone can help me with a method.


    Data sheet will always have the employee on column A, and the helper column on the last row. (I've created the helper column since it's unique and I thought it would help)


    Rows with data should be randomly picked from the "Data" sheet based of criteria announced on "CRIT" worksheet from the same workbook.


    For each individual employee(column "A") a fixed number of rows will need to be extracted (randomly) as indicated in "CRIT" sheet (The Max Rows Of UniqueID).

    And here comes the twist: The UNIQUEID(helper column) it's composed of 2 separate processes and there's a Max Number for each of them also. (For example if we have 7 rows extracted, we won't have just 7 randoms, but would have 5 and 2 or 4, 2, 1 based of the numbers in the "CRIT" sheet, which are listed also)


    There's also an importance which marks which "process"( Data E vs. Data F) should be more present in the final picks.


    Another important aspect I can think of relates to when isn't enough data to perform the above distribution. Maybe an error should occur pointing it out, asking to verify "Data" sheet?


    I have read about different methods like scripting dictionary & filters but I can't seem to compile anything.

    Your work & time will be greatly appreciated!

    Let me know if this is doable and if I can be of any help!

  • Sla Hours Between Two Dates

    Hello Wizards,


    I am having a hard time trying to calculate how many hours each user worked on a task(Item Number) based off a given report which is non-malleable.


    Each Item Number has a total time(SLA hours) which I was able to calculate with the below formula, excluding weekends, holidays and outside working schedule hours. Seems to work well, except when PM end time > start time. (It's probably bc of the US hours approach, maybe someone can propose something better? )


    Formula:

    '=(NETWORKDAYS.INTL(A2,B2,1,G$2:G$4)-1)*("17:00"-"8:00")+IF(NETWORKDAYS.INTL(B2,B2,1,G$2:G$4),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1,G$2:G$4)*MOD(A2,1),"8:00","17:00")



    For each Item Number, every time the user changes the owner it's leaving a comment, which leaves a date, picked by the report. If it's moved, until the task it's being returned, the other user is accountable for the time.


    I would need a formula to substract from the whole business working time how much time every user spent on the task. (back n forth between them)


    Is this achievable?

    Thank you in advance!!

  • Macro to fill word template

    This is exactly what I needed. Thank you for all your time & help Roy, you are amazing mate! You just saved me a ton of time!

    I will leave the post unresolved 24 more h in case someone got to work something and wants to contribute, then I will mark it as resolved:thumbup::thumbup::thumbup:

  • Macro to fill word template

    Brilliant idea Roy, I have a feeling this will work as intended but right now I am experiencing Run time error 91: object variable, on line Set docWord = objWord.Documents.Add("Path")