Interactive Summary

P

panzram

Does anyone know a way to create an interactive/revolving formula that would
automatically refresh daily?

Here's what I'm trying to do:

I have a list of Doctors, I need to track their daily number of biopsies and
patients seen. I need to create a simple daily summary for my director that
will
report the pertinent numbers but refresh automatically the next day when new
numbers are entered in another worksheet (within the same workbook). Make
sense?

I appreciate any and all help.
 
Y

Yacbo

If you want Excel to automatically make a new worksheet for each day, format
it, and then establish links from it to a master summary worksheet, you are
probably getting into the realm of programming (though I can’t assert that
with absolute certainty). However, unless your data is enormously more
complex than indicated, a new worksheet for each day seems like overkill. A
static worksheet for each doctor with a summary worksheet seems more
appropriate.

For example, I created a short table as follows in a worksheet named “Dr Jâ€

Date Patient Biopsy
================================
1/14/2007 John Smith 1
1/14/2007 Jane Smith 1
1/15/2007 John Jones 0
1/15/2007 Jane Jones 1

My summary page, named “Summary†looks like this:

Dr J
==================================
Date Patients Biopsies
==================================
1/13/2007 0 0
1/14/2007 2 2
1/15/2007 2 1

For above, the formulas are as follows, which is where the “automatic†comes
into play.

Date: =IF(AND(A1<>TODAY(),A1<>""),A1+1,"")

Patients: =IF(A1<>"",COUNTIF('Dr J'!$A$1:'Dr J'!$A$100,A1),"")

Biopsies: =SUMIF('Dr J'!$A$1:'Dr J'!$A$100,A1,'Dr J'!$C$1:'Dr J'!$C$100)

You would copy and paste these formulas down the columns as needed. In this
fashion, your “Summary†page would be automatically updated daily under the
typical settings of Excel, with a new line appearing daily. The first date
(1/13/2007 here) is manually typed in and the others follow automatically.
If data entered in the “Dr J†sheet exceeds 100 records, replace 100 (above)
with 1,000, 10,000 or any number you need. If this doesn't help, tell me
more about the situation and I'll either point you in the right direction or
admit that your problem is beyond my knowledge.
 
M

Max

One possibility ..

A sample construct is available at:
http://savefile.com/files/414328
Interactive Summary.xls

Assuming identically structured source sheets
named as eg: Doc1, Doc2, Doc3, etc, viz:

In sheet: Doc1
Date Biopsies Patients
01-Dec-06 5 12
02-Dec-06 1 11
03-Dec-06 3 14
etc

In sheet: Doc2
Date Biopsies Patients
01-Dec-06 4 12
02-Dec-06 1 14
03-Dec-06 2 9
etc, and so on

In sheet: Summary,

Create a data validation droplist in A2 to allow easy selection of the
summary field of interest, eg: Biopsies, Patients

Click Data > Validation
Allow: List
Source: Biopsies, Patients
Click OK

Then list the sheetnames (doctor's names) in B2 across. Ensure these names
are consistent with the names entered on the actual tabs. List the dates in
A3 down

Put in B3
=IF(OR($A$2="",$A3="",B$2=""),"",SUMIF(INDIRECT("'"&B$2&"'!A:A"),$A3,OFFSET(INDIRECT("'"&B$2&"'!A:A"),,MATCH($A$2,INDIRECT("'"&B$2&"'!1:1"),0)-1)))
Copy B3 across / fill down as far as required to populate the summary table.
The table will return the required results for the selected field (in A2)
from all the source sheets.

For a neater look, suppress the display of extraneous zeros in the sheet via
clicking: Tools > Options > View tab > Uncheck "Zero values" > OK
 
P

panzram

You're AMAZING!!! Thank you so much for your help. I never would've figured
that out myself.
 
Top