Hi,
I have 7 different spreadsheets for colleagues timesheets on a daily and weekly bases.
I want all this data to be combined into one overall spreadsheet but I'm not sure how to do this easily.
This is what I have so far:
I have the first week of the 7 employees' data which I have done by using the following code if you will:
='C:\Users\user\Documents\Timesheets\New Timesheets\[Agenda_User1.xlsx]Sheet1'!D12 (D12 is the cell within that sheet)
So for another day in their spreadsheet I had to insert the following:
='C:\Users\user\Documents\Timesheets\New Timesheets\[Agenda_User1.xlsx]Sheet1'!D15
I had to do this for all users (all 7) which is taking a lot of time.
When I had the first week combined I thought I could then highlight the text and then drag and complete with the bottom right hand side corner. When I done that, the cells changed to where they are placed.
e.g. for the next week user1 should have seen this:
='C:\Users\user\Documents\Timesheets\New Timesheets\[Agenda_User1.xlsx]Sheet1'!D19
but it turned to become this:
='C:\Users\user\Documents\Timesheets\New Timesheets\[Agenda_User1.xlsx]Sheet1'!D55
due to the other users data getting in the way.
I have tried to use the =index but I'm not that advanced to use it correctly.
Please note that the overall spreadsheet is in a separate one and not on another sheet inside.
Thanks
I have 7 different spreadsheets for colleagues timesheets on a daily and weekly bases.
I want all this data to be combined into one overall spreadsheet but I'm not sure how to do this easily.
This is what I have so far:
I have the first week of the 7 employees' data which I have done by using the following code if you will:
='C:\Users\user\Documents\Timesheets\New Timesheets\[Agenda_User1.xlsx]Sheet1'!D12 (D12 is the cell within that sheet)
So for another day in their spreadsheet I had to insert the following:
='C:\Users\user\Documents\Timesheets\New Timesheets\[Agenda_User1.xlsx]Sheet1'!D15
I had to do this for all users (all 7) which is taking a lot of time.
When I had the first week combined I thought I could then highlight the text and then drag and complete with the bottom right hand side corner. When I done that, the cells changed to where they are placed.
e.g. for the next week user1 should have seen this:
='C:\Users\user\Documents\Timesheets\New Timesheets\[Agenda_User1.xlsx]Sheet1'!D19
but it turned to become this:
='C:\Users\user\Documents\Timesheets\New Timesheets\[Agenda_User1.xlsx]Sheet1'!D55
due to the other users data getting in the way.
I have tried to use the =index but I'm not that advanced to use it correctly.
Please note that the overall spreadsheet is in a separate one and not on another sheet inside.
Thanks