I have two worksheets. On one (called €˜clients) I have a list of clients
names (column A) and for each client a number of other columns of data,
including one for sales value (column B). The data is not sorted at this
stage but I wish to be able to sort it by various columns.
On the other worksheet (called €˜media) I wish to group the clients by
advertising medium and produce a sales total for all the clients contributing
to each sales medium. For example, clients D, B and A were achieved through
TV advertising and have a combined sales total of $100, while clients E and C
were from radio advertising and have combined sales of $150. My formula (on
the media worksheet) for the €˜TV row is a simple
=clients!B1+clients!B2+clients!B 3 (D, B, and A are on rows 1, 2 and 3
respectively)

My problem is that when I sort the table in the clients worksheet (which is
originally in the order of D, B, A, E, C) on column A, the formula on the
media worksheet stays the same and now gives €˜TV the total for clients A, B,
and C instead of D, B and A. If I dont sort the data but I add rows on the
client worksheet the column/row references in the formula change so its
still adding the correct figures, but when I sort the data the references are
not updated.

I hope someone can give me some clues as to how I can solve this problem.