Hi

It's because you link to cell, not to record in main table.
You have to redesign it a bit. And there is no need for different report
sheet for every state too - a main sheet and a single report sheet will do.

Delete all your state sheets.

Create a sheet States, with a singel-column table State, and enter all
states there.
Define a named range States (InsertNameDefine) with source:
=OFFSET(States!$A$1,1,,COUNTA(States!$A:$A)-1,1)

Create a sheet StateReport
A1="State:"
To cell B1 apply data validation list (DataValidation, and select List from
drop-down) with previously defined named range as source:
=States
Define a named range RepState
=StateReport!$B$1
A4="Name"
B4="Month"
C4="Amount"

On sheet Master, add a column to left of your table (it will be column A
now, with your original table starting from column B). For consistience,
enter some heading for it too, p.e. Index.
A2=IF(AND($B2<"",$D2=RepState),COUNTIF($D$2:$D2,$ D2),"")
, and copy it down at least for length of Master table (but you can have it
further down prepared for future entries). The formula must return order
numbers for records in Master table, with state same as selected on report
sheet, and return blank cells for all other records. You may hide this
column now, when you are afraid it will be confusing for user.
Define a named range Master
=OFFSET(Master!$A$1,1,,COUNTA(Master!$B:$B)-1,5)

On sheet RepState
A5=IF(ISERRROR(VLOOKUP(ROW()-4,Master,2,0)),"",VLOOKUP(ROW()-4,Master,2,0))
B5=IF($A5="","",VLOOKUP(ROW()-4,Master,3,0))
C5=IF($A5="","",VLOOKUP(ROW()-4,Master,5,0))
Copy A5:C5 down for as much rows as you think you'll need to get all records
for any of states returned.

Now you can select any state in cell B1 of report sheet, and a table for
this state is returned.

You also can easily design another report sheets based on Master table, like
monthly report (where you select month, and all records for selected month
are returned), yearly report (you select a year, and summary amounts for all
names are returned), state monthly report, where you select a state and a
month, and all records for this state for this month are returned) etc.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Susi Farmer" wrote in message
...
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