Need help using data from multiple worksheets to create a Summary report

I hope someone can help me

I have just started with a reasonably large homeless organisation
where they
collect large amounts of statistical data using excell. There are 25
induvidual services hence 25 induvidual data sets. Each set
essentially
contains the

1. a referral worksheet - name date etc (Monthly Tally of referals)
2. an induvidual stats sheet a variety of data (for each referal that
becomes a client)
3. an occupancy stats sheet (Like a tally sheet 1 counts for each  day
+ a Bfwd and Cfwd column)
4. a monthly summary report (combines data from all)

Whilst a Database comes to mind at this time they wish to stick with
excell.
At the momment the whole process is manual each spreadsheet is filled
out as
needed at the end of the month it is printed off and then manually
entered
into the report spreadsheet (errors galore) which is then printed off
manually checked for errors and manually corrected.  I am wanting to
merge
the 1,2 and 3 above into a single Worksheet that will capture all the
input
data. This part is fine I can handle this. I then want to be able to
have a
second worksheet automatically updated with just the client data. and
a third
worksheet that provides the report (this part is ok as well ive
figured it
out)

Where I need help is how do i automate the process of getting the data
from
worksheet 1 to 2 based on certain criteria ie

First Wrksht
       A           B             C             D
E
F               G
1. Name	 Name	DOB	AGE	Sex	DOB	Accepted
2. Fred 	Smith	**/**/**	16	m	**/**/**	yes
3. Mary 	Brown	**/**/**	17	f	**/**/**	yes
4. John	Brown	**/**/**	23	m	**/**/** 	yes
5. Gary	Elmore	**/**/**	18	m	**/**/**	no
6. David	Neal	**/**/**	18	m	**/**/**	yes
7. Tracy 	Duggan	**/**/**	15	f	**/**/**	no
8. David	Smith	**/**/**	16	m	**/**/**	no
9. Bill	Jones	**/**/**	18	m	**/**/**	yes
10 Susan	Bray	**/**/**	15	f	**/**/**	yes
11. Liz	Grey	**/**/**	16	f	**/**/**	yes								 Second Wrksht

       A           B             C             D
E
F               G
1. Name	Name	DOB	AGE	Sex	DOB	Accepted
2. Fred 	Smith	**/**/**	16	m	**/**/**	yes
3. Mary 	Brown	**/**/**	17	f	**/**/**	yes
4. John	Brown	**/**/**	23	m	**/**/**	yes
5. David	Neal	**/**/**	18	m	**/**/**	yes
6. Bill	Jones	**/**/**	18	m	**/**/**	yes
7. Susan	Bray	**/**/**	15	f	**/**/**	yes
8. Liz	Grey	**/**/**	16	f	**/**/**	yes

so the second worksheet only shows accepted clients I can then use
this
wrksheet to provide the data for my formulas in the third wrksht which
is the
monthly report.
Note the number of rows each month will change based on No of
referals

I hope I have made sense Im sure that there is a way of doing this but
im
just at a loss

please help anyone
0
TThomson (2)
7/31/2008 10:09:32 AM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
641 Views

Similar Articles

[PageSpeed] 32

One way of transferring the data from Sheet1 to Sheet2 would be to use 
Autofilter to filter on Column G for accepted entries only and then copy 
band paste the result to sheet2.

You could write a Macro to do this automatically.

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"tedt" <TThomson@syfs.org.au> wrote in message 
news:1a3c1dea-1c8b-4ce4-abb7-b7f43d6f56ee@t1g2000pra.googlegroups.com...
>I hope someone can help me
>
> I have just started with a reasonably large homeless organisation
> where they
> collect large amounts of statistical data using excell. There are 25
> induvidual services hence 25 induvidual data sets. Each set
> essentially
> contains the
>
> 1. a referral worksheet - name date etc (Monthly Tally of referals)
> 2. an induvidual stats sheet a variety of data (for each referal that
> becomes a client)
> 3. an occupancy stats sheet (Like a tally sheet 1 counts for each  day
> + a Bfwd and Cfwd column)
> 4. a monthly summary report (combines data from all)
>
> Whilst a Database comes to mind at this time they wish to stick with
> excell.
> At the momment the whole process is manual each spreadsheet is filled
> out as
> needed at the end of the month it is printed off and then manually
> entered
> into the report spreadsheet (errors galore) which is then printed off
> manually checked for errors and manually corrected.  I am wanting to
> merge
> the 1,2 and 3 above into a single Worksheet that will capture all the
> input
> data. This part is fine I can handle this. I then want to be able to
> have a
> second worksheet automatically updated with just the client data. and
> a third
> worksheet that provides the report (this part is ok as well ive
> figured it
> out)
>
> Where I need help is how do i automate the process of getting the data
> from
> worksheet 1 to 2 based on certain criteria ie
>
> First Wrksht
>       A           B             C             D
> E
> F               G
> 1. Name Name DOB AGE Sex DOB Accepted
> 2. Fred Smith **/**/** 16 m **/**/** yes
> 3. Mary Brown **/**/** 17 f **/**/** yes
> 4. John Brown **/**/** 23 m **/**/** yes
> 5. Gary Elmore **/**/** 18 m **/**/** no
> 6. David Neal **/**/** 18 m **/**/** yes
> 7. Tracy Duggan **/**/** 15 f **/**/** no
> 8. David Smith **/**/** 16 m **/**/** no
> 9. Bill Jones **/**/** 18 m **/**/** yes
> 10 Susan Bray **/**/** 15 f **/**/** yes
> 11. Liz Grey **/**/** 16 f **/**/** yes Second Wrksht
>
>       A           B             C             D
> E
> F               G
> 1. Name Name DOB AGE Sex DOB Accepted
> 2. Fred Smith **/**/** 16 m **/**/** yes
> 3. Mary Brown **/**/** 17 f **/**/** yes
> 4. John Brown **/**/** 23 m **/**/** yes
> 5. David Neal **/**/** 18 m **/**/** yes
> 6. Bill Jones **/**/** 18 m **/**/** yes
> 7. Susan Bray **/**/** 15 f **/**/** yes
> 8. Liz Grey **/**/** 16 f **/**/** yes
>
> so the second worksheet only shows accepted clients I can then use
> this
> wrksheet to provide the data for my formulas in the third wrksht which
> is the
> monthly report.
> Note the number of rows each month will change based on No of
> referals
>
> I hope I have made sense Im sure that there is a way of doing this but
> im
> just at a loss
>
> please help anyone
> 


0
sandymann2 (1054)
7/31/2008 12:12:44 PM
Reply:

Similar Artilces:

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

Looking for Excel Help
I'm a very novice Excel user and am looking for a little help with creating a formula for a spreadsheet I'm creating for my personal use. I would appreciate some assistance if possible. Thanks in advance. Dan --- Message posted from http://www.ExcelForum.com/ Hi Dan! Post a sample of what you want to do. Your question is just a tad open ended <g> -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "DanB4105" <DanB4105.ywtpa@excelfor...

Reporting from Project Server
I dont know if i need to ask this question here or in the Access section. I have an ODBC connection to the Project Server database so I can make reports through Access. Access' limit of 255 fields per table is causing me some trouble. for example, the MSP_VIEW_PROJ_PROJECTS_ENT table has well over 255 fields. Access only shows me the first 255 fields. how can I change that so I can see all the fields in that table? thanks, Hadi Hadi, I have not tried this yet it may be a viable option. Have your DBA create a view that pulls the key fields to this table and the specifi...

Autonumber created.. problems in the future?
I managed to create an autonumber in Microsoft CRM. I did this by making a field "Number"(in the database "New_Number") and I published it on the form. Then I went To the SQL server and I changed the field in the table to Identity Yes, Identity seed 1, Identity Increment 1. I locked the field on the form. It worked! I think that this is not supported by Microsoft. But has anybody got any idea which troubles i could get with this configuration? San ________________________________ Do you know all add-ons for Microsoft CRM? Visit http://www.pimpmycrm.com The biggest dange...

Need Help, Task Start Date is wrong
I’m using MS Project 2007, have several task linked with finish to start. I have set date to schedule from, hours per day set to 8 and Working Monday thru Friday. My schedule shows Task 1 Duration 4 days, start Wed 6/2/10, Finish Mon 6/7/10 Task 2 Duration 3 days, start Mon 6/7/10, Finish Thu 6/10/10 Task 2 should have a Start Date of 6/8/10 not 6/7/10; what is causing this? Thanks in advance for your help. ...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

Certain Keys/Characters not recognised when creating a task
I've just attempted to create a task and the edit control for the subject of the task refused to accept the characters c h s t u and v. I was able to switch to other applications such as a command prompt and internet explorer and type the characters quite happily (so there is nothing wrong with the keyboard) but when I switch back to Outlook it will not recognise them. I'm using Outlook2003 as installed with Office 2003 Professional (SP1 and all other updates applied). As a last resort I closed down Outlook and restarted the program which is now accepting the keys/characters. A...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

print multiple pages on one sheet of paper
I am using mailmerge in Publisher to create placecards for a party we are hosting. The final size of the placecards is 1.5" by 1.5" and we have to print 100 final cards. Publisher gives me the option of printing multiple copies of the same page on one sheet of letter sized paper or one page on one sheet of letter sized paper. What I would like to do, however, is print multiple different pages on one sheet of paper. If I cannot find a solution for this, I will need to print 100 separate pages with a 1.5" square box of copy in the center of each sheet. In page setup, sel...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

I need a macro
I would like a macro to do the following: I would place the the cursor in any cell and this macro would give me the average of all the entries to the left if the selected cell. EX: I place the cursor in cell M12. I want this macro on display in cell M12 the average of all the values from Col C12 to Col L12. Note that there may NOT be entries in all the cells in that range. Also the start point would always be col C .Thanks You can do this easily with a worksheet_selection event in the sheet module but you would probably want to restrict to a certain range or columns and rows or it woul...

explanation of codes in Visual Basic when creating User form
Hi, I am trying to create a user form in Visual Basic however I'm trying to teach myself by reading/watching tutorials. (www.contectures.o.ca, etc) A lot of the instructions I am seeing simply give the code rather than explain how to actually write one from scratch. So... I need to know what each 'term' means so I can understand how the codes work. Any help is much appreciated :) One of the first codes is for the Add button Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("PartsData") What d...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

How to create an autonumber field?
hi i need to create an autonumber field to automate account numbering. how can i do this? thanx You can do this using a post callout piece of code so when you update an account this code is called which calls back into the platform and works out the last account number then adds one to it and updates the account record. look on msdn.microsoft.com under crm for examples -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Max" <Max@discussions.microsoft.com> wrote in message news:0ABFF244-EC0A-48EC-9E76-7CA61E6EBC3A@microsoft.com... > hi > > i need ...

Math Worksheet Database
I am interested in developing/acquiring an Access 2003 database to generate (K-5 grade) math worksheets where I can track student results. I would hope not to reinvent the wheel. Thank you for any assistance. ...

Cannot open Outlook, keep getting Send error report of Microsoft.
We are having problems with Outlook 2003. Everytime we try to open, we get this: Send error report to Microsoft. Any ideas why? All windows and office update have been completed. ...

Please help #8
I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or know how to fix it? Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of memory or system resources. Close some windows or programs and try again.' "John S" wrote: > > I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or > know how to fix it? > > Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of > memory or system resources. Close some windows or programs and try again.' > >...

I need to print a word in PDF from excel.
Hi, In my excel Macro, I update several values and then open a word linked with that excel. Finally what I need to do is to print that word to PDF. I already have a PDF Printer, so I just need the code to do it. Can anybody help me? Thanks in advance. Gast=F3n. Habilita la impresora predeterminada como la PDF y ya lo puedes imprimir a al formato deseado Gracias Francisco,=20 Tienes alg=FAn codigo para ello? gracias Repitiendo: Desde el panel de impresoras asigno primero la que me interesa y despues ejecuto mi macro desde el archivo excel asignando la instruccion sig: midefaultes = a...

predict future data
Is there a way to create an XY line graph wih plotted data, yet leave room to predict future data on the axes? I can get the graph, but the x and y axes stop at the last data points, and I want those axes continued so that the existing data can be examined and future data predicted and plotted on the same graph, but I am not sure how to accomplish this. Any suggestions would be appreciated. Thanks. Jeff 1) Click on data series in chart, use Add trendline; in Option tab specify some units forward OR 2) Read Help about TREND and FORECAST, and SLOPE and INTERCEPT OR 3) Get crystal bal...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

MOVE TO FOLDER... only appears. I need MOVE TO FOLDER
This is very odd and I've found that I've had this problem before with not finding icons. Some I've found at the office don't appear here and vice versa, or they act differently. I have Outlook 2000 in both places! Very odd. At home, I couldn't find the HIGH IMPORTANCE icon under the list of items available in the customize. Yet I have it at work. That's the one where when the HIGH IMPORTANCE is on, it shows a depressed button state. I really need that in both places. Anyway, simple (or so I thought) - I ended up just brining the toolbar from work on a floppy, a...