Getting several breakdown sheets from one input sheet

[apvost]

I have a large sheet that many different users input data to. One column is in fact the user that input the info. I would like to keep this general sheet for all to use for input, but then have a seperate sheet for each user that would show the data they put in. I would like this to update the individual sheets automatically, not only when the info is first input, but also as any changes are made to the info. Any help is greatly appreciated, thank you in advance from a first-time poster!!

 

[agihcam]

Hi-
Welcome to the board.
is it a shared workbook? also what particular info you're trying to track? please explain more.

 

[apvost]

It is a workbook located on the in-house network, available to all authorized users. It lists medical information for patients who may/may not be eligible for a research study. Most of the info is dates, i.d. numbers, and a few simple if/and/or functions to determine eligibility. I am simply looking for a good way to have a tabulated sheet for each person, there are only 8, that takes the info from the main sheet so I can track how many patients come from each person, and each patients info. It would serve as a summary for me, as I have to report how many people get how many patients, and how many patients are eligible, etc.

-Andy

 

[agihcam]

post a few sample data using htmlmaker;

http://www.puremis.net/excel/downloads.shtml

 

[apvost]

Book1
	A	B	C	D	E	F	G
3	Doctor	Date of Visit	Date of Surgery	Patient i.d. #	Eligible	Enrolled	Comments
4	Smith	9/15/2006	12/15/2006	123456789	Y	N	Likes Cheese
5	Jones	9/20/2016	11/24/2006	987654321	N	N	Doesn't want to participate
Sheet1

Random sample I made from memory as I'm no longer at the office. This is the main idea only there are about 15 columns and about 500 rows. The name is what would be the variable for deciding which sheet the info goes to. Also I may not need to have all the info sent to the separate sheet, just select columns. Thanks for the HTML tip.

-Andy

 

[agihcam]

Andy -
from your data, you could have a lot of row records for doctor Smith for a different patient right?

 

[apvost]

Correct. Some doctors have 100+ patients on the list( each row is a new patient) and some might only have 10-20 patients. Hence one reason for wanting a nice way to tabulate the info into doctor specific sheets.

-Andy

 

[agihcam]

ok. if you are familiar with a macro, this code is straight forward that you can easily understand.

Sub test()
Dim i, ii, iii, iv, v As Long
i = Sheets("main").Range("h" & Rows.Count).End(xlUp).Row
ii = Sheets("main").Range("a" & Rows.Count).End(xlUp).Row
'delete individual doctors sheetname
Application.DisplayAlerts = False
For v = 2 To Sheets.Count
    Sheets(2).Delete
Next
Application.DisplayAlerts = True
'get unique doctor's name and put in columnH
Range("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "H1"), Unique:=True
'add sheet for each doctor's
For Each c In Sheets("main").Range("h2:h" & i)
    Sheets.Add after:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = c.Value
Next
Sheets("main").Columns("h").ClearContents
'get doctor's name from main and put each records in doctor's sheet
For iii = 2 To Sheets.Count
    For iv = 2 To Sheets("main").Range("a" & Rows.Count).End(xlUp).Row
        If Sheets(iii).Name = Sheets("main").Cells(iv, "a").Value Then
          Sheets(iii).Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 8) = Sheets("main").Cells(iv, "a").Resize(, 8).Value
        End If
    Next
Next
End Sub

if did not want a macro, there is a very powerful excel features known as MSQuery, this way you dont need codes.

 

[apvost]

Thanks a million, I'll give it a shot.

-Andy