Same data on multiple tabs

[thalantyrdsl]

Hi all,

Very novice question here.

I have three departments at my work that want to be able to contribute information to the same source data, but on 'their own tab'.

So we'd have Sheet1 with 6 columns of data - eg: name, age, dob, profession, country, language, Columns A,B,C,D,E,F.
Then we'd have Sheet2, Sheet3 and Sheet4, each with the same data, BUT they want to enter their own info into Columns G,H,I,J.

Now I know that I can make the data on Sheet1 into a table and then on Sheet2 just do an =Table1 and it will all display, that's fine. Also works with a power query.

The problem comes from when they start entering data in Column G,H,I,J and then we INSERT a NEW ROW on Sheet1.
Their information is now stored against the wrong row number because it's not stored WITHIN the table so the data doesn't shift downward.

I've tried entering new columns into the table on Sheet2 but because it's just showing the Table1 it doesn't allow it.

Google searches haven't been helpful because I can't figure out the right term to search for.

Any help would be appreciated.
Thank you in advance.

 

[James006]

Hi,
You did spot the crucial point ...but on 'their own tab' ...

A properly structured database is kept in one single sheet ...

And then all your potential Reports can be done automatically, in less than one second, with a Pivot Table ...

 

[thalantyrdsl]

Hi,
You did spot the crucial point ...but on 'their own tab' ...

A properly structured database is kept in one single sheet ...

And then all your potential Reports can be done automatically, in less than one second, with a Pivot Table ...

I do agree, but in this case I want their data separated.
We're talking about 15 additional columns per department, over 3-4 departments. That's a lot of horizontal scrolling.
I can collate the data back together in a power query later if we need to put some pivot tables in but wanted to keep this data separate.

Thank you for the reply.

 

[thalantyrdsl]

Please note that this is cross-posted.
Same data on multiple tabs

Thank you in advance.