Links not updated when primary worksheet is sorted

[Guest]

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!

 

[Arvi Laanemets]

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 (Insert>Name>Define) 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 (Data>Validation, 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.

 

[Guest]

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

 

[Arvi Laanemets]

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
(Data>Pivot 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

 

[Guest]

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