Thank you so much for all your help - I really appreciate it!!
--
Susi


"Arvi Laanemets" wrote:

Hi

(Sorry, but I'm on annual leave currently, and the return address was at my
work)

You can do this easily using pivot table.

Select the range with master table, and invoke Pivot Table Wizard
(DataPivot Table and PivotChart Report);
Check 'Microsoft Excel List or Database' and 'Pivot Table'. Next;
Set destination (new or existing worksheet);
Press on Layout button;
Drag State and Name fields into Row area (or one of them into Row, another
into Column Area);
Drag Amount field into Data area - there must appear field Sum of Amount.
When it is something else, double-click on field to change the summarizing
function. OK;
Finish.

It's sorry, but you cant use dynamic named ranges as Pivot Table source with
Excel2000 (I'm not sure about later versions). So you have to redefine the
source every time when you add data to Master table, or you give the source
range with some amount of empty rows at bottom. When later, you'll have
values '(blank)' for both fields 'State' and 'Name' along with those entered
by you.

Whenever you enter new data into Master sheet, or change existing ones, you
have to refresh the pivot table to get changes into it - select any cell in
returned table, right-click to open a drop-down menu, and select '!' . You
also can set various Table Options for Pivot Table, p.e. the table to be
refreshed whenever the workbook is opened.


Arvi Laanemets


"Susi Farmer" wrote in message
...
Arvi, I hope you're able to get this follow up question (I tried your
personal e-mail address but didn't hear back from you).

Your response to my query on linking and sorting worksheets was amazing
and
right on. In my scenario, where I needed one Master report and a report
by
state, I need to further complicate matters by providing subtotals and a
grand total by state. (So, let's say in each state, there are multiple
sales
persons and I need to subtotal by sales person and grand total by state.)
Is
there a way that I can do this, too?

Thank you again for all your help! I look forward to hearing from you.

Regards,
Susi Farmer
--
Susi


"Susi Farmer" wrote:

I've got a workbook with multiple sheets in it. The first one is a huge
file
(35 columns and 1,500 rows) with complex functions. Subsequent sheets
are
linked to the first. To illustrate my question, I'm using a simplified
scenario:

-Sheet 1 is the "Master" that has sales data: Name, Month, State, Dollar
Amt.
-Sheet 2 is linked to sheet 1 and picks up the CA sales data directly
(ie: I
went into Sheet 2 and typed =sheet1!a1, etc. for all the CA sales data in
Sheet 1)
-Sheet 3 is also linked to sheet 1 and pickes up sales data from another
state
-Sheet 4, ditto for another state.

My problem is that when I sort the data in Sheet 1, my links do not
follow
my data, rather they continue to reference the linked cell. (If I have a
link in Sheet 2 to =Sheet1!A1, when sheet 1 is sorted, that reference
stays
the same although the data is now in cell A10.) My linked references are
not
absolute, so I'm not sure why the links are not following the data.

Hope someone can help! Thanks!

--
Susi