Forum Discussion
Create 'parent' table to update data in 'child' worksheets + data in 'child' worksheets must tally
Hi all - I am working on a Cloud Excel file to collaborate with many other parties (who are all basic users of Excel).
The purpose of the worksheet is to create an assessment/checklist template, to assess a large volume of submissions/projects.
Hence, I require the parties to provide their assessment criteria to a 'parent' worksheet. This 'master' worksheet should be automatically duplicated to other 'child' worksheets to assess individual project submissions.
In the 'child' worksheets, there will be additional columns for the collaborators to fill out, to assess the individual project submissions.
The problem is that the 'parent' worksheet is still subject to changes e.g. adding/deleting rows, re-arranging rows, etc. At the same time, the collaborators are giving inputs in the additional columns in the 'child' worksheets.
Is there a way to ensure that the additional data in the 'child' worksheet still tallies with the correct row of the referenced data/table from the 'parent' worksheet?
An example of what I am trying to do in the 'child' worksheet (i.e. specific is as below.
Thanks!!
| A | B | C | D | |
| 1 | Referenced from Parent sheet) Collaborator's Department | (Referenced from Parent sheet) Assessment Criteria | (Child sheet) Remarks specific to individual project submission | (Child Sheet) Checkbox for collaborators to tick off if assessment criteria is applicable to project submission |
| 2 | Dept A | Criteria 1(1) | [For Dept A to remark] | <Checkbox> |
| 3 | Criteria 1(2) | [For Dept A to remark] | <Checkbox> | |
| 4 | Dept B | Criteria 2(1) | [For Dept B to remark] | <Checkbox> |
| 5 | Criteria 2(2) | [For Dept B to remark] | <Checkbox> |
Above is a 'child' worksheet.
Columns A & B will automatically be updated from a 'parent' worksheet (a "Master" template)
Columns C & D will be editable in the 'child' worksheet. Inputs in columns C & D will be unique to the individual 'child' worksheet.
If Dept A adds a new row between Rows 3 & 4 in the 'parent' worksheet, I need the 'child' worksheet to create the new row between Rows 3&4, so that the custom inputs in Columns C & D (e.g. Cells C4, C5, D4, D5) will still refer correctly to Dept B's criteria, after Dept A adds the new row.
1 Reply
I guess a database web application with login is suitable for Similar situation as below:
Due to various reasons, the customer's delivery date needs to be advanced or postponed. Every month, when making the production plan, the planner needs to inquire with the sales department about the adjusted delivery dates for all orders. If the planner sends the master sheet via email for each sales person to fill out, data leakage between different personnel may occur. Alternatively, the planner can send individual sheets to each sales person, but this results in multiple emails being sent and then merged into a master sheet. This process is time-consuming and labor-intensive.
Currently available conditions:
Each sales person has a fixed notebook login username.
There is a shared network file folder within the company.
The planner sets up a shared folder in the shared drive and shares the master sheet, setting read/write permissions for each sales person.
What is desired:
Each sales person logs into the shared network folder and opens the shared spreadsheet.
They can only see the content under their own name in the sheet.
They can only edit the adjusted delivery date column; the rest of the sheet's content is protected and only viewable.
