Posts by tr1face

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!

Hey royUK, I have explained the req in a new sheet in the file. It's an array with transpose as I see it

Are you trying to add date into filename?

Hello,

It should not be a hard task for someone who knows arrays. Details are in the example workbook, Can anyone help me accomodate following request?

Thank you,

Hey royUK, can you please take a look on this topic and advice if it's achievable? Thank you!!

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

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?

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!

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

sorry, =iferror(vlookupformula,0). Add the iferror to your vlookup formulas. If the product was not sold will return 0. If 0 is returned, my formula will skip that value

ig the easiest way would be to avoid #NAs in the first place. Try adding =iferror(vlookupformula,"")

Then u can use the formula

If your columns amounts are bigger than 0$ you can try: =SMALL((M2,O2,Q2),Index(frequency((M2,O2,Q2),0),1)+1) in cell I2, draggin down after

Many thanks to Roy, thread is now marked as resolved

upgraded to office16, works like a charm now :d thread resolved, thank you!!

Hey Roy,

Sorry to bother again but I'm keep getting errors regarding missing library 16, ig I'm using an older version than yours, it there a quick fix regarding to binding for this?

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

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

Hey Ron,

Attached word and excel with some raw data. Column A will have unique numbers, so it can create a word template for each row

you can fill any dummies in the excel file, while numbering the rows in column A

Column A will have only unique numbers starting from 1