Hi Mynda
I developed an excel based time reporting system for our company.
Each employee saves a timesheet template (T_EMPLATE.xlsx) with their name (e.g. J_Brown.xlsx, J_Jones.xlsx).
The same folder also contains a summary file (Summary.xlsx) that consolidates the data from individual timesheet files. This file contains a sheet per week that pulls in data for 60 or so employees against 80 or so project codes. Each column is header by employee names from a list on the Setup sheet in the same file, Gaps are left available in this list for new employees as 'T_EMPLATE'. Similarly I have left columns available in each sheet for new employees linked to T_EMPLATE.xlsx.
When a new employee joins, I currently have to manually update this summary file, swapping out T_EMPLATE.xlsx with the new employee file reference in an available column of all week sheets (grouped).
I want to know how I can automate this, so that by simply adding a new employee's name or filename to a list on a Set-up sheet within Summary.xlsx, that individual's file would be picked up by the Summary file look to a different file if the name is changed.
Similarly, moving people between departments is a manual task. Department information is available in each individual's timesheet file, but I am unable to create the formula to reference a different file is the column header name changes.
I expect there is an easy solution to this, but I've been looking for a few years with no success. If I could solve this, our system would be full automated with minimal effort to add or change employees.
Regards
Declan Slemon
Aerogen Ltd
Hi Declan,
It's difficult to give you formula advice without seeing your file, but the process you describe is something Power Query (available in Excel 2010 onward) is designed to automate.
With Power Query you can create your summary file from the individual workbooks, as opposed to having to maintain the summary file and all of its formulas. You'd put all of your time sheet workbooks into a folder and Power Query would collate the data from those files into a table in a new file. If you added a workbook to the 'time sheet folder' you'd simply click the 'Refresh all' button and Power Query would get the new data.
Instead of having the employee's department name in their file, you'd have a lookup table that maps each employee to a department, which you can then merge with your Power Query data. If an employee changes department, you'd change the lookup table and this would feed through to the Power Query data.
This post shows how to use Power Query to consolidate data on multiple worksheets in the same file, not quite the same as your scenario with the data in different files, but it'll give you an insight into Power Query and might help you visualise how it can help.
I don't recommend you attempt this without learning Power Query first. While Power Query is fairly intuitive to use and quick to learn, as with anything, you have to set things up right otherwise you'll run into problems later on. On my Power Query course page here there is some more information on Power Query and how you install it etc.
I hope that points you in the right direction. Please let me know if you have any questions.
Mynda
Hi Mynda
Thank you for your response and for directing me to Power Query. I will look further into that functionality for future projects. However, in this incidence, the Summary file has lots of other information such as quarterly resource forecasts and reports by department & project that are used by our company. I'd prefer not to rebuild the file at this time as it is a source link for many reports.
My query really only relates to the following
I have simple formulae of this nature in columns, starting from row 2;
='C:Documents[J_Brown.xlsx]Summary'!P2 (Formula A)
Where J_Brown.xlsx is an individual employee's timesheet file.
The result of these formula is the number of hours assigned to a project for an individual in a specific time period.
The header of the column, row 1, is populated with the individual's name from a lookup table in a setup sheet in the same file.
=IF(Setup!G$2="","",Setup!G$2) (Formula B)
The format of the names in the lookup table is actually the full filename.
J_Brown.xlsx (Result B)
I am interested to learn whether I can replace the "J_Brown.xlsx" text in Formula A above with an expression that would use the value in the row 1 column header cell to find the relevant employee's file rather than having to specify it in Formula A.
I could send an example file if that would help, but was unable to attach in this Forum post.
Any assistance on this specific query would be most appreciated.
Regards
Declan Slemon
Hi Declan,
You would have to use the INDIRECT function e.g. =INDIRECT("'C:Documents"&FormulaB&"Summary'!P2") , but as INDIRECT is a volatile function, excessive use of it will make your workbook slow.
Plus, the file you're referencing must be open, or INDIRECT will display #REF! errors.
Probably not ideal.
Just to clarify, you can add Power Query to your existing file. You don't have to start a new file from scratch as it's not a special file type or anything like that.
Mynda
Thanks Mynda
I'll try the INDIRECT function before looking further into Power Querys.
Regards
Declan
Hi Declan,
Try the PULL_A_CELL function. It is a small user defined function included inside an Add-In I created
http://www.excelnaccess.com/connect-multiple-workbooks/
This function takes the following syntax and will work perfectly in your case (without the need to have the workbook open as required by INDIRECT function)
=PULL_a_CELL(WBpath,Sheetname,celladdress)
=PULL_a_CELL("C:Documents" & FormulaB ,"Summary",P2)
Regards,
Zubair
Thank you Zubair
I apologise for missing your suggestion until now.
Will this functionality continue to work if additional workbooks are added to the folder as new employees are added?
Do all users of the Summary file require your add-in?
Regards
Declan
