I am new to the forum but am already very thankful I've found it!! I would consider myself moderate in my expertise of Excel.. I certainly know more then most people I know but am often overwhelmed by what it's capable of doing. My first question is something I would have previously thought was very basic, yet I can not get it to work. I am working now with Excel 2010, where previously I was working with 2007 in my last job. I am also in a situation where most of my files are on a shared network with my co-workers, whereas previously I owned the only copy of most of my documents... so I am not sure if that is playing into my issue.

I am a construction manager for a company that does retail millwork installs. I have about 200 stores and 4 install companies for which I need to track data. PO#'s.. to contract values, store names, addresses, install dates, crew members... etc. It's a lot of information. In the past I would've handled this by having an individual workbook to track each store seperately and would link the information from the "Master Schedule" (note: the Master Schedule is a "live document" on a network drive that is opened and modified by anyone who wants to/needs to do so)... I'd like to have it be that when the Master Schedule is updated with revised quotes... change in Install Team... change in Install Date etc... all of that information would update and change automatically in the individual project workbook.

The issue I am having, which is making me insane, is that the Master Schedule is formatted as a table so that each column can be sorted/filtered by the data in it. Say I want to look at stores alphabetically... or by Install date.. I can sort/filter by that column. BUT when I do sort as needed the linked data isn't locked in.

For example.... lets say when my Schedule is sorted by "Install Date" New York, NY Store 1234 is in Cell B6... but then I sort the sheet by "Contract Value" New, York, NY Store 1234 moves down to Cell B17... My individual tracking sheet for New York, NY store 1234 now reads the store information which moved to Cell B6 because of the "Contract Value". So since this is a document I mostly control.. however don't always, I can't guarantee that my Individual install tracking workbook's integrity will remain intact.

I hope that makes sense... and I hope there is a way to fix this. I've never ran into this issue before. The way my excel has always worked is that link carries the data in the cell.. not the cell and carries whatever data happens to be in that cell.

HELP ME
This is just the beginning of what I would like to do in order to get this all tracked properly and efficiently... but not being able to work this out has held up my progress all around.