Posts by Sleeplol


    Is there a formula for returning a cell value from a closed WB using variable file paths???

    In Reports WB

    Column A is updated with a number of file paths (around 100), but will change as files are added or deleted from folder.

    I'd like for column B to be able to return cell B4 from each of the file paths listed in Column A.



    Would you help me with adjusting the below code to accomplish the following?

    • Open file in a directory with a specific title
    • Title is based on cell value
    • Open different file types (pdf, word, excel, picture, etc)

    I've been using the following script for a different project, however, it will only search within a 1 level of sub-folder, and only if the file is a PDF.

    We would need to be able to search a directly due to the required file organization into different sub-folders within a directory.

    Help would be super appreciated


    Hi Roy,

    Attached is the Workbook with the Google Sheet connection. I turned off Refresh on Open

    Note, the JotForm form is simply:

    Enter Employee ID

    Clock IN

    Clock OUT

    (In and out are radio buttons)

    Also, the formulas were working fine for day shifts, but now that there are night shifts they are not properly capturing the shift.

    So, all the pay periods before last week were correct and were able to be emailed to payroll, but last week, period between 10-4 and 10-10 everyone's time is messed up.

    We have a lot of red tape with permissions and such.

    It would be simpler to use an all excel method with the clock in and out in the workbook. But that just isn't going to be available.

    We are able to set up a simple In & Out on jot form on a thin client computer, then HR has access rights for office to use the excel file from office computers.

    Later today when I'm at work I could attach the actual doc so you can see how it's put together; and hopefully give your feedback.

    Do you use excel to keep track of your employees? If so, is the data sent from a form? And would you mind sharing the method?

    Hi Roy,

    They clock in using a JotForm which saves to a Google Sheet which is then queried to an Excel Workbook.

    That query populates TblA. TblB needs to be structured like the example in my last post so that the user can run a report from the dashboard which uses an Advanced filter macro.

    The value of the entire doc is based on the structure and accuracy of TblB

    I posted an example workbook with no connections to just show the data and an example of how I need TblB structured.

    - Show Clock in and out times for each employee ID

    - Accommodate for day and overnight shifts (e.g. 8/3/20 7:00 PM - 8/4/20 5:00 AM)

    - Show a blank or short message when employee forgets to clock in or out (e.g. "No Clock IN" - 8/4/20 5:00 AM, or 8/3/20 PM - "No Clock OUT")

    Hi Roy,

    Thanks for taking a look into this

    All of the data is queried into TblA

    I'd like the end result to be TblB so that we can easily see the hours worked in a shift.

    Desired result would be TblB format but would also accommodate for day and night shifts and indicate if someone didn't clock in or out:

    Employee IDClock InClock OutDuration (hrs)
    121210/01/20 6:00 PM10/02/20 5:00 AM11
    232310/2/20 4:00 AM10/2/20 1:00 PM9
    343410/2/20 8:00 PM10/3/20 5:00 AM9
    232310/4/20 12:00 AMNo Clock In

    Please let me know any more info I can provide

    Hello All,

    I'm attempting to restructure a table with another table.

    How would you do the following???

    All Clock IN's from TblA in TblB Clock In column and Clock OUT's in the Clock OUT columns

    This would also need to line up the appropriate Employee ID. I've also attached a macro enabled example file for testing


    Time IN or OutEmployee IDINorOUT
    10/9/20 6:00 AM1212Clock IN
    10/9/20 6:30 AM2323Clock IN
    10/9/20 6:00 PM1212Clock OUT


    Employee IDClock INClock OUT
    121210/9/20 6:00 AM10/9/20 6:00 PM
    232310/9/20 6:30 AMetc..

    I appreciate any help with this.

    Good Morning Everyone,

    I can't seem to get this to calculate correctly, so maybe this would need a VBA solution.

    (I don't want to get docked for a double post; so I'll just post in the VBA section)

    Table ClockIn is queried so it will grow.

    Table Data_tbl Needs to split the Clock In and Clock Out times into different columns, and have a row for each entry. It works fine until the 12th entry, then it repeats.

    File attached.

    thanks for any help

    Timezone UTC Employee ID: No Label IP Employee Employee ID Clock IN Clock OUT Clock IN
    8/6/20 7:02 AM 8907 Clock IN James Dean 8907 8/6/20 7:02 AM 8/6/20 3:05 PM Clock OUT
    8/6/20 9:21 AM 6422 Clock IN Clark Kent 6422 8/6/20 9:21 AM 8/6/20 8:07 PM
    8/6/20 3:05 PM 8907 Clock OUT James Dean 8907 8/7/20 7:05 AM 8/7/20 2:49 PM
    8/6/20 8:07 PM 6422 Clock OUT Clark Kent 6422 8/7/20 2:49 PM 8/7/20 2:59 PM
    8/7/20 7:05 AM 8907 Clock IN James Dean 8907 8/10/20 7:09 AM 8/10/20 3:08 PM
    8/7/20 2:49 PM 6422 Clock IN Clark Kent 6422 8/10/20 8:24 AM 8/11/20 7:56 AM
    8/7/20 2:49 PM 8907 Clock OUT James Dean 8907 8/11/20 7:04 AM 8/11/20 3:09 PM
    8/7/20 2:59 PM 6422 Clock OUT Clark Kent 6422 8/11/20 7:56 AM
    8/10/20 7:09 AM 8907 Clock IN James Dean 8907 8/12/20 7:04 AM 8/12/20 3:02 PM
    8/10/20 8:24 AM 6422 Clock IN James Dean 8907 8/13/20 7:05 AM 8/13/20 3:02 PM
    8/10/20 3:08 PM 8907 Clock OUT James Dean 8907 8/14/20 7:08 AM 8/14/20 3:00 PM
    8/11/20 5:08 PM 6422 Clock OUT James Dean 8907 8/17/20 7:06 AM 8/17/20 3:11 PM
    8/11/20 7:04 AM 8907 Clock IN James Dean 8907 8/18/20 7:02 AM
    8/11/20 7:56 AM 6422 Clock OUT James Dean 8907 8/18/20 7:02 AM
    8/11/20 7:56 AM 6422 Clock IN James Dean 8907 8/18/20 7:02 AM
    8/11/20 3:09 PM 8907 Clock OUT James Dean 8907 8/18/20 7:02 AM

    Hi RoyUK,

    Yes, it is unrelated.

    It was literally my very next task with that same project, but I see that I should have posted a new thread to add value for anyone else who happens to be searching the topic down the road.

    I will re-post

    Man, Mumps and Roy in 1 post, I've gained like half of my VBA knowledge from just you two lol


    I went ahead and targeted an "Action Item" to each drop-down that you input for me.

    Kind of longhand on my part, but there were only 10.

    Hey, while you're here (if you happen to revisit this thread)

    I'm attempting to abc sort and eliminate blanks in multiple columns independently of each other.

    Currently I'm using the following then calling each macro, but with a large data set it could take a few seconds.

    (Note: I'm only posting the first 3 columns for the sake of space; there are more)

    How would you recommend doing this??

    Hey Mumps,

    Thanks for your help, I always appreciated your scripts.

    The transfer works well but I can foresee a couple issues occurring.

    Issues one: If a user selects the "blank" more than once, other action items are deleted.

    Issue two: The copied issue under Action Items need to always be on the same row as the original issue.

    Issue two is my fault for not stating that this is a necessity. That little area that the info is copied to won't be seen by the user and will feed a database.

    Hello Everyone,

    I'd like to set up a check box in E23 to:

    1 - add "Action Item" to G24

    2 - Value from G22 is copied to O9

    Redundancy: (If check box is "deselected"

    1 - "Action Item" is removed from G24

    2 - O9 Value is cleared

    This process is repeated for ten "check boxes" and their corresponding areas

    Currently I have a worksheet_SelectionChange handling select & deselect, however, it doesn't call any function.

    I can do this long hand with a change even for each cell, but with my know how I would need to have a separate cell for "Select" and Deselect"

    I'm hoping you'll help me code this the proper way, versus what I would come up with.

    Moch WB is attached, and probably makes better sense than what I've described.

    Thanks for any help.

    Hey Justin,

    Yes, you're right, I was thinking of "regular formulas" vs "array" (though powerful; in my experience take longer to calculate)

    I would've also thought that a VBA solution would be quicker, but Roy's knowledge of VBA doesn't really leave room for my assumption

    Thanks for your response and effort putting together the Pivot Table

    The issue is that I would like the report to end up looking like the PreReport so that the date and instructor are also displayed. Below is the same pic from the WB.

    Columns A:D are "refreshed" whenever a new training is added and assigned via userform (this is assigned to groups in bulk). The range on Sheet SignOffDates is also updated via userform.

    The PreReport format seems to work well when running reports powered by advanced filter. The layout also feeds a dynamic matrix for ease of viewing for employees and management.

    My mistake was setting this up prior to testing the performance of so many array formulas. Whenever a new training is added and assigned, it can take 5 to 7min to update, without the formulas (or when calculations are temporarily set to manual) it renders in 3sec. Though it will have to calculate at some point, and it is likely that management will want to add a new training then immediately run a report to notify staff of an update or what not, and the wait time makes that really tough.

    Though the PreReport format would work best for me, I believe that you and Roy have the next best solution by using a pivot table or Power pivot (since combining two ranges)

    Still very open to help.

    In the mean time I'll post updates to this thread as I discover solutions.

    Would there be a way to convert the array formulas to native formulas? Even if the formulas are longer and require helper columns, it would probably run faster than having all of the arrays. Seems like Excel is optimized for its native formulas