Linking Sheets - Master sheet with multiple related sheets

AFore

Hi!

I am looking to create a master sheet that contains personnel data elements used by multiple teams with different needs. The master sheet should allow data entry of new attendees while also pulling new entries from a related travel sheet.

Example: 

Master Sheet Admin is aware of a new registrant and manually adds to the master sheet. That entry would then be visible on the travel sheet (accomplished with cell linking) so the travel department can take action and update the travel elements that will link back to the master sheet.

Additionally, the travel department may be aware of the registrant first and would add the new registrant in the travel sheet which would then add the new registrant to the master sheet.

How do I set the sheets up to allow for new entries in either sheet? The cell linking will be reversed depending on which sheet the information is entered in first.

Do I create two sections in both sheets using the indent feature and then format the cells under the two sections to point the correct way?

Is that the easiest way?

Thank you!

Andrée Starå

Hi,

Have you thought about using a report instead?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

AFore

Thanks for the response. I think I leaned toward using sheets with cell linking because of some of the editing limitations with reports (which I could be wrong about). I'll revisit, though, and see if that will work.

Thanks again!

Andrée Starå

Happy to help!

Let me know if you have any questions!

Steve K314

I know this is old, but if it helps others - no real solution above.

I found the best way to do this for my needs is to have one master table/sheet for the people, in my case it's a project sheet. The first step to any process form me is setup the project - all shared relevant data is there. This is also where I manage my Client Dropdown list, so I have ONE place to add/remove/update clients and the data links with the below method to every other table.

You can automate an alert that a new entry is there… perhaps its a new-person check box for each department… or there is a workflow that hits one department first (every time) then alerts the other when the first is done. If you want it to be a specific order, I may use 2 separate check box columns… but there are multiple ways to handle it.

Now with each sheet, you don't want the same data that can be "changed" in one place and throw data out of sync, so I use Index Match function. All I need to know is a Project ID that links all data (person ID for you)- then in each table, I enter in a project (or person) ID that will then link the relevant cell data for each column you need in the other tables. If you change the data on the master sheet, the changes follow.

When you make your entry form, you can auto check (or leave unchecked) the column check box (and hide this from the form so no one changes it.) When that new entry arrives, automate the alert to the departments. Then when they do what is needed, they either check or uncheck the box (based on how you want to think about it). Then it clears off their to-do report. or it color codes or what have you.

Based on what you mentioned, I would create an alert with the new persons name and ID listed, then have an entry form where the user enters that ID in the form along with all the other info they need to enter… but the actual name and contact info and anything else in the main table it linked into the appropriate cell (Index/Match method) all they do is add the new info in the form. For convenience, I embed a report in the dashboard with open items on it (new people for example)- this makes the display of the Person's ID easy. I also put a link in the form to look it up if needed. This makes the workflow easy - with quick access to the data needed to complete the table.

Once they do the work - you can automate the check box (or make them manually do it when entry is complete) so the item can fall off of the to-do list report.