Script created by u/Wasserdemon 7/16/2019 for u/Jacobelation

Introduction:

This folder contains four types of files. There are two scripts, the sample file generation script and the overview generation script.
The sample file generation script has been included for academic interest/testing. Folder also contains the excel files generated by the test script
and the overview excel file which will serve as the central sheet. The employee records hold hours for one week of work.
The overview sheet should allow a user to input a week number and generate hours for each employee for that week.

Briefly, the overview generation sheet script does the following:

	1. Iterates through employee records (those excel files that aren't the overview)
	2. For each record, it checks to see if the employee is already included in the overview
		2a. If not, a new sheet in the book is created with the employee's initials
	3. Puts the week number and hours in week-order in the employee-specific sheet.

	! At this point the overview sheet has been fully updated with new employee hours !

	4. The overview sheet is setup
		4a. Place instructions in A1
		4b. Creates a header in B1, C1, ... for each non-overview sheet which shares the employee initials
		4c. Places a formula in the next cell down which pulls relevant info

Installation Instructions:
(To be completely replaced when complete package is released, for now will require Python3 Dev environment)

1. Go to https://www.python.org/downloads/ and download the appropriate version (requires 3 or better)
2. Start Python3 installer, make sure to include PIP installation
3. Open command and type "pip install openpyxl"
4. Move generateOverviewFile.py to the same folder containing all relevant workbooks including overview
	4a. This step may be skipped to maintain file artchitecture, see modification section for pathing details
5. Update generateOverviewFile.py OR file architecture rules to match, see modification section
6. Run script and verify accurate results.

VLOOKUP Formula:

VLOOKUP formula is as such: =VLOOKUP(target, range, column # to return, FALSE)
INDIRECT allows the string in a cell to act as a reference for a sheet name. 
So the formula pulls the target week # from cell A2, uses the column header to determine
which sheet to use (range is always A:B with this format), and picks the hour column to
return. i.e. Type 16 into A2, the rest of the row will fill in with the hours each employee
accrued during week 16.

VLOOKUP will not function if the item being searched for is not in the first column

Pre-Modification or What Formats is the Script Seeking?:

Script assumes that employee record files are stored in the same folder as the script and
that filenames are in the format of [employee first and last initial]_[week#].xlsx

Record file is assumed to contain employee initials in B1, the week # in D1, and hours in A2

Script will not create a new overview workbook but attempt to load and modify a file named "Overview.xlsx"
I recommend creating a blank sheet named Overview before running the script initially or changing
the filename to your current overview sheet.

Modification Checklist:

	1. The files cannot be located in a central location:
	
		The solution is to use direct path names in place of the indirect ones
		used in the script. 

		Lines 14 and 78 refer to 'overview.xlsx', you may replace this path/filename
		with your own. If you use a different name and all records are stored in one
		location, update the if statement on line 24 with your overview filename.

		Line 22 contains a commented item which you may modify to set the directory 
		in which employee records are kept. Simply delete the hashtag and put your path
		where the brackets are now.

	2. The records or overview sheet cannot be formatted to match script assumptions
		
		Script looks for employee initials on line 31 at position B1. B1 may be
		updated to the coordinates at which an employee identifier is located.

		Script looks for week number on line 48 at position D1. D1 may be updated
		to the coordinates at which week number is located.

		Script looks for hours on line 49 at position A2. A2 may be
		updated to the coordinates at which hours are located.

		Script looks for a sheet names 'Overview', if you would like the main sheet
		be named differently, replace 'Overview' on lines 62 and 69 with the name of
		your main sheet.

		! Most of the lines after 41 format the overview sheet and applies the 
		  VLOOKUP formula. This section is fragile. Unless otherwise specified
		  above, please do not modify these lines as this could easily break the
		  script. 

Troubleshooting:

	Script will not function if any of the relevant files are open.