Excel Help - formula to pick up stock levels in another spreadsheet

Occasional Visitor

Hi, i currently use excel for a client where we keep a master stock tracker of their items we hold in stock. On a separate document we detail items we have reported missing from recent store visits which then need replenishing  on our next visit, at the bottom each item totals up to tell us how many is needed throughout the entire state. I then have a row under the total for each item to show me what is in stock on the master stock tracker. Now the master stock tracker is always changing and new items adding, so i'm finding that the formulas  i am using in the report doesn't move with the item in the stock tracker. For example 'Blue' is picked up from A5 but then i add 'Black' in front of 'blue' which makes 'Blue' now B5 but the formula in my report is still picking up A5...how do i get the formula to keep with the right cell when it moves?? below is an example of the formula i am using which means whenever i add something to the stock tracker i have to constantly update the formulas in the report which is time consuming as there is 100 plus items on the report

='[Stock Tracker.xlsx]Sheet1'!$A$5

i have tried removing the $ signs but it still doesn't work.

One thing i thought might be an issue is that sometimes i will update the stock tracker whilst not having the report open so is this an issue that they both need to be open to replicate the changes? Thanks for any help offered 

0 Replies