Posted a question and received a response from a really knowledgeable guy about using Power Query. Don't know if that response takes my question out of the larger groups' access so I am reposting. His response also mentioned his thought that I was building an APP. The "APP" part of my workbook is already built and working. I need to extract data from the sheets resulting from execution of some macros and create a Summary worksheet. Here is the logic I think is what I need to pursue the VBA code for.
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.
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
- 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,
- 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
