Daily summary worksheet that extracts data from up to 40 worksheets and emails the sheet as an excel attachment.

[juscuz419]

I have a timesheet workbook that creates and modifies up to 40 timesheets. I need to send a daily summary sheet to an email address each morning for the individuals' activities for the previous day.

Need to go to the first sheet that has a name like XXX_1234A (where XXX is any three letters and 1234A is any 4 numbers followed by an A, D, or WG.

Then extract first and last name, place an X in a field if they have a vehicle (this is determined on the sheet by a TRUE occurring for one 3 fields None, Personal, or Rental), the start time, the end time, the status (M, O, S, D), the total hours (for that status), if there is a second (or 3rd or 4th) status for that person, fill in the a next row with the start time, end time, status, and total hours

Then go to the next sheet and get the same info until ALL sheets have been "looked at".

I THINK I get the logic but get lost when I try to make it happen.

 

[awoohaw]

What version of excel are you using? I suggest using Power Query. There are plenty of YouTube lessons on how to combine files.
Please look at the YouTube channels by Mr. Excel, ExcelIsFun, Leila Gharani, and MyOnlineTrainingHub.

After you set up the Power Query structure it is a simple daily update.

Here is a video playlist from MyOnlineTrainingHub that I often go back to and review.

https://youtube.com/playlist?list=PLmd91OWgLVSKnVrL0YxdOH61MAiqlFHac&si=xSUVUwTdD7goPNGu

 

[juscuz419]

I watched 3 tutorials on Power Query and it seems like a great tool. I am not sure that this is where I need to learn how to use it because of the complexity of what I need to do. I am going to start the learning process, though.

What I have is one workbook that is event based. For each event, there is a Master Timesheet and individual timesheets for each person that deploys on the event. The number of people (and subsequent timesheets) can vary between 2 and 40 for each event and the workbook creates an individual timesheet for the people by running a macro against a roster on user command.

• 1. What I need to do right now is from a Summary worksheet, first enter a date then
• 2. Go to the Master timesheet and retrieve Crew Name, Client, Storm and place them in fields on the summary sheet.
• 3. Then I need to go to the first individual timesheet and retrieve the First Name, Last Name, Employee ID and then look at 2 checkboxes and place an X in a field on a row of the summary sheet if either one of them is TRUE
  • A. Then go to the first row in the actual timekeeping section of the sheet and IF the date in a field in that row on the timesheet is the same as the one selected in the first step above,
    • A1. get the status (M, O, D, S – could have as many as 3 rows on a single day), and the corresponding start and end times and total hours for each status, then count whether there is a value in three fields in the row and enter the result in a field on the same row of the summary sheet, then go to a field in that row of the timesheet and determine if there is a value in it and put Yes in a field on the row in the summary sheet row if there is
    • A2. Then look at the next row on the timesheet and IF the date is the same, execute A1 again. If it is Not, then move to B
  • B. Move to the next individual timesheet and repeat A above
  • C. When there are no individual timesheets that have not been "read", go to 4
• 4. open an Outlook email, with the subject of “Summary Sheet for (Date originally entered)”, attach the Summary sheet as an excel attachment, Mail Body of “Attached, please find the Daily Summary for (date originally entered) type in an email address and send it

There are probably a lot of other more elegant ways to do this. If someone has a clean way to do it, I would greatly appreciate the help.

 

[awoohaw]

It looks to me like you are building an application. I've done similar things like this in MS-Access. It is probably doable now in excel with user forms, vba, and power query. I think once you learn power query you'll figure out how to accomplish the task.

Best wishes to you.

And, by the way, please update your profile so your excel version shows up on your button.

 

[juscuz419]

I REALLY need some help on this. I can handle the emailing and the first 6 lines in the logic below, but the rest of it I am having to do manually. That involves touching 40 WS on 5 different WB on a daily basis. There HAS to be a way to code the colored font logic below. Manually it takes a couple of hours and this is all volunteer work. Help!

On the WS “Daily Summary” enter information in 8 Cells

One of these is a date in N1

Go to WS “Master Timesheet”

Get value in M2 and put it in A4 on WS “Daily Summary”

Get Value in M1 and put it in A6 on WS “Daily Summary”

Get Value in H2 and put it in E6 on WS “Daily Summary”

Go to first WS with name LIKE “XXX_####X” where X can be any letter and # any number

If the date in C18 is equal to the date in N1 of WS “Daily Summary”

Get the value in F4 and put it in B21 on WS “Daily Summary”

Get the value in M4 and put it in C21 on WS “Daily Summary”

Get the value in Q1 and put it in D21 on WS “Daily Summary”

If the value in AB3 or AB4 is “True” then the value in E21 of WS “Daily Summary” is “Pickup/SUV” otherwise it is blank

Get the value in F18 and put it in G21 on WS “Daily Summary”

Get the value in G18 and put it in H21 on WS “Daily Summary”

Get the value in H18 and put it in I21 on WS “Daily Summary”

Get the value in E18 and put it in F21 on WS “Daily Summary”

Count the non-blank cells in K18, L18 and M18 and place the result in K21 WS “Daily Summary”

If N18 is NOT blank then the value in L21 of WS “Daily Summary” is “Yes” otherwise it is blank

Go to the next row of the WS and If the date in C19 is the same as the date in C18 then loop through again placing the new data in the next row of the WS “Daily Summary”

Repeat this loop until the date in the “C” cell is not the same as the date in N1 of WS “Daily Summary”

Go to the next worksheet that matches the LIKE name criteria and repeat the looping for that WS

Do this until there are no more WS matching the Like name criteria