There are a few ways of doing this. One is simple but takes a lot of work, and the other is complex but better.

Assuming your starting data is fixed in size and location, you can make a reference to it (that equals cell thing) that references data on other sheets. In not on a computer to check what works, but I think it is as simple as copy the cell you desire and paste that into the other sheet. In the new sheet, you should see not only a cell reference but also a sheet reference. 

The complicated way is to learn VBA: Visual Basic for Applications. There is a programming language built into Excel (and other office products too) that you can use to manipulate data in any way you need.

Another one is that you can use Power Query. Make sure your input data are correctly organised and remain organised when you update them. Turn your data into an Excel Table by putting the cursor somewhere in your data and pressing Ctrl+T. Make sure it chooses the correct range and that your column has headers. Click OK and give your table a right name.

Now, create a Query from Table/Range and delete the columns from that query that you do not want on your new sheet 1. Close and Load. Here is your new sheet 1!

Create another Query from your Table/Range and delete the columns you do not need in your new sheet 2. Make sure this query has a different name to Query 1. It should have but make sure—Close and Load. Here is your new sheet 2.Your two new sheets are set up now and whenever you update your input table, your queries will be updated too although you might need to refresh them.

Don't forget, you can include all sorts of changes and calculations in your Queries as you wish

I hope this helps!