Creating Linked Worklist with Excel

[EpicDave]

I create worklist for my employees and they work on there worklist. I would like to create a master worklist that links to the other tables so when I update the table all of the work they have done on there table will appear on my table. I have tried a couple of different ways and can't seem to get it to work.

Powerpivot- I used to do this with powerpivot but my new employer did not get the right excel liscense that come with powerpivot (even though it is 2013)
Data Query- I have tried this a couple of times but can't seem to do it with multiple workbooks.

Anyone have any other suggestions or can link me to a thread that goes over this that would be great.

 

[nardagus]

I don't know how your excel files look like but if you have two opened files and in first file in a cell (lets say A1) you put = (equal) sign and then click on a cell in a second file (lets say A1) you will get both cells linked (it's like you'd do a formula). This means that if you put some value in a cell A1 of a second file, you will see this also in a linked cell of a first file. Couple notes though:

1) When all files (master and emplyoees files) are all opened, all links are refreshed automatically every time you put something in source files. However if all files are closed and you open a master file only, excel will ask you for a permission to refresh the data. Depending on amount of files and the data inside them it can take a bit.

2) Master file and employee files don't have to be in one directory, however master file has to have an access to employee files. So it would be the best if your employees could place their files somewhere on a network share on your local server. If it is not possible and you want them to send those files by email then you can create a folder in your computer and place all files (including master file) there and then create links between files like I described above. The only problem is that send and then saved files have to have the same names as at the time you created links. Otherwise you will get errors during data refresh or it will refresh properly but you will have an old data. With files on a network it is less likely that files names will change.
Of course there is also data security and confidentiality. If an employee can see files of other employees than you can use network share. Otherwise you need to use email or other way to get their files.

3) You shouldn't move employees files to other locations. Everytime you move them into another location you will have to change links in a master file. That doesn't apply if all files (including master file) are in one folder.

This is the easier part of linking files together. However again: I don't know what your files look like and how much data is there.

Nard

 

[MarkReddell]

I believe that any ver. of excel 2013 has pivot tables available. However not the online ver. of excel. NOTE: You can link your master source date to any other workbook by way of copy & paste special - paste Links!!!

 

[FDibbins]

Can you give more details please.
Do your employees have their own files
How often is the update done
are the layouts and formats the same on all files (including yours)
What data are you dealing with
Can you share a sample of what you have - and what you want

 

[MarkReddell]

Have U resolved your issues???