linked workbooks - could that solve my challenge?

[Gingertrees]

I get requests from businesses around North America to have training on our products. Based on state, these training inquiries need to go to different sales reps.

I was thinking, if I maintain these requests in one big workbook... is there a way to automatically link any requests for e.g. California to a separate workbook for the California rep? Assuming both of these workbooks exist on the same server.

I know how to parse out a workbook of existing data, but I want this to automatically update. So I can parse what I have today, but if I add a request for Joe's Widgets of Sacramento, I want that to add to the other workbook automatically.

Can this be done? I've never done this sort of thing with linked workbooks before. Much obliged for any answers or links to more info.

 

[Jerry Sullivan]

Hi Gingertrees,

One option would be to set up Query Tables in each rep's workbook that import the latest data from your master.

This thread has some step by step instructions for xl2007 or later.

http://www.mrexcel.com/forum/excel-questions/680325-autosort-date-after-formulas-updated.html

The only modification I think you'll need to those instructions is that when you get the dialog window titled "In the Query Wizard - Filter Data:"
first enter your filter cirteria (IE State field equals "California") before hitting Next.

 

[Gingertrees]

Hi Jerry,
This looks promising, but I'm a little confused. It looks like I need the data in my master workbook to become dynamic named ranges. OK, done. The odd thing: I have Office 2010, but when I look for data sources in the "From Other Sources" area, it looks for .xls files (and cannot find all the areas on my server, but I presume that's a network problem on my end). Is the .xls search normal?

Also, am I on the right track with naming the main sheet in my master workbook as a dynamic named range?

 

[Jerry Sullivan]

It's best if you use a named range for the data in the master workbook, but it should not be a dynamic named range.
It should be a named range with a fixed address reference. (You could use a simple macro to resize the name to the CurrentRegion each time you save the workbook).

In MS Query, the file type default is (*.xls*) which encompasses *.xlsx, *.xlsm and *.xls.