Automatic update of information in a spreadsheet

[Guest]

Hi,
I am using Microsoft Excel 2000. I have just done a spreadsheet which
contains information about patients. In one worksheet I have the raw data and
then in others I have copied columns across and then used IF(AND) statements
to abstract the information I need, e.g. to work out how many males there are
in each of the hospitals. What I need to know is how do I set it up so that
when a new entry is added to the raw data, i.e. another patient in a new row,
it automatically updates the information in the other worksheets? What is
happening at the moment is when I add another row the IF statements only work
up to the cell that I put the formula in, so I have to go through every
worksheet and drag down the formula into the new cells in every column, which
takes a lot of time. Can you please help me?

 

[Dave Peterson]

Excel doesn't push data to other worksheets very well, but it can pull data very
nicely (with =sheet1!a1 type formulas).

But it kind of sounds like you're doing summary statistics based on information
in your main data table.

I find it much easier to keep all my data together on one sheet and then
manipulate that the way I want.

You may want to look into pivottables based on dynamic ranges.

For information about dynamic ranges, visit Debra Dalgleish's site:
http://www.contextures.com/xlNames01.html#Dynamic
(the range the name refers to can expand/contract whenever you add/delete an
entry)

And for pivottables:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx