Getting several breakdown sheets from one input sheet

apvost

New Member
Joined
Oct 4, 2006
Messages
7
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!!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Hi-
Welcome to the board.
is it a shared workbook? also what particular info you're trying to track? please explain more.
 

apvost

New Member
Joined
Oct 4, 2006
Messages
7
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
 

apvost

New Member
Joined
Oct 4, 2006
Messages
7

ADVERTISEMENT

Book1
ABCDEFG
3DoctorDate of VisitDate of SurgeryPatient i.d. #EligibleEnrolledComments
4Smith9/15/200612/15/2006123456789YNLikes Cheese
5Jones9/20/201611/24/2006987654321NNDoesn'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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Andy -
from your data, you could have a lot of row records for doctor Smith for a different patient right?
 

apvost

New Member
Joined
Oct 4, 2006
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
ok. if you are familiar with a macro, this code is straight forward that you can easily understand.
Code:
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.
 

Forum statistics

Threads
1,136,992
Messages
5,679,018
Members
419,799
Latest member
APInfa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top