View Single Post
 
Old 07-11-2019, 01:50 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Am I right assuming, you have several users, everyone of them working with his/her own workbook with some standard design. And you have some manager working with some main workbook, where he is setting tasks for users? And this manager needs a worksheet, where he/she can see the status of all tasks?


When yes, then I have done something like this before.


All workbooks must be available for all people involved and stored in local network resource(s);
It will be best to use defined Tables in all workbooks;
To exchange data between main and users workbooks, when the total number of tasks is some hundred, you may go with links to read data, otherwise you have to use SQL queries;
In main workbook must be a table where all tasks are entered, with a field for some ID of users, tasks are assigned to. All tasks must also have some unique ID;
In user workbooks must be along with task table a sheet with a table which reads all assigned tasks for particular user from main workbook (main info table). This table must have a column which indicates, is the task present in tasks table or not. And in case links are used, this table must have enough empty rows prepared to easily last for any possible number of task for this user in workbooks lifetime;
In tasks table, a validation list must be used to select task ID-s from main info table, and there must be some indicator (e.g. Conditional Formatting) which indicates any double entries of task id, so user can avoid them;
In main workbook must be hidden sheets for every user workbook, where info from task tables of users workbooks is read. Again, when links are used, there must be enough rows prepared for every user;
Then in main workbook another hidden sheet is needed, which reads info from user tasks hidden sheets (summary sheet). Maybe you can use UNION query for this - I haven't tried this. Or you must have enough predefined rows with formulas (like you have 3 user sheets with 100 predefined rows for every one, then in this sheet 1st 100 rows are reading data from 1st user sheet, next 100 rows are reading data from 2nd user sheet, etc. And to avoid rows having no data displayed, you have a column where row number is calculated. And user id must be there for non-empty rows too;
The last step is add columns into tasks table of main workbook, which are reading data from summary sheet.


Now manager can use autofilter to get whatever info he/she wants to see. When there is a need for some summary info for made selections, you can place some fields with SUBTOTAL() formulas at top of page (leave an empty row between table header and summary fields) and freeze headers and all above them.


Edit: Both links and queries read data from saved file on disk somewhere. When user works with file, he/she is working with virtual copy of file in his/her computer's memory. The changes made in this virtual copy are written into file only when saved. When there is need for operative info to be read at another file, then the user working with file has to save frequently!

Last edited by ArviLaanemets; 07-11-2019 at 02:35 AM. Reason: Additiona advice
Reply With Quote