Linking/auto-filling data from one worksheet to another

[jackmcgilligan]

Hi all!
I'm currently doing a stock take of items at work. These items will be entered into a document with quantities and storage locations. Others will be using this document to search for items they are I need of, and when they take items, are required to edit the quantity.

The main worksheet is a 'master', while the other worksheets are separated on the basis of their storage location. I would like these storage location worksheets to change as the 'master' sheet changes.

I initially did the simple format of ='Worksheet1'![cell location]. But I noticed that if I changed the way that Worksheet 1 (Master sheet) was sorted then it would change what the formula above resulted in. That is, Worksheet 1, A2 might have contained the data set =14 (just for examples sake). But, when I change the way this same sheet is sorted, A2 might now contain the data set =197. So, the storage location worksheet changes from 14, to 197 respectively.

Is there a way to link data from one worksheet to another, one which works around this issue?

Feel free to ask any questions for further clarification on the issue.

Thanks all!

[#2]

@jackmcgilligan I would get rid of separate sheets for each location and "calculate" stock on hand based on opening stock for each item, plus all incoming minus all outgoing transactions. Build a transaction list, so to speak. Summarise stock by item by location from that transaction list, using one or more pivot tables for instance.

But if you really need to work with separate sheets per location, I would make sure they all have the same format/structure and use Power Query to append them when needed and create a master list. This master can be sorted in PQ or Excel without affecting the underlying data (i.e. the separate sheets).

Just refresh the PQ created table and status of the local stock at that moment will be reflected. Note that there are no direct formulae between linking the sheets/tables. A different method, but more robust than working with linked sheets, in my opinion.