| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help using data from multiple worksheets to create a Summary
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 (Monthly) 2. an induvidual stats sheet (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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help using data from multiple worksheets to create a Summary
Hi Ted
You may enter this formula in cell Sheet2 A2 =IF(Sheet1!$G$2="yes",Sheet1!A2,"") Then just copy it and paste it on B2:G2 Thanks "Ted Thomson" wrote: 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 (Monthly) 2. an induvidual stats sheet (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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help using data from multiple worksheets to create a Summ
Then remove the $ signs from the formulas in all row 2 cells so you can copy
those and paste them down. "Farias" wrote: Hi Ted You may enter this formula in cell Sheet2 A2 =IF(Sheet1!$G$2="yes",Sheet1!A2,"") Then just copy it and paste it on B2:G2 Thanks "Ted Thomson" wrote: 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 (Monthly) 2. an induvidual stats sheet (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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help using data from multiple worksheets to create a Summ
Thanks Farias i think this is what Im looking for some one gave me a vba
macro to use with auto filter but it seemed a little complex and didnt actually acheive what I wanted ill let you know how I go "Farias" wrote: Then remove the $ signs from the formulas in all row 2 cells so you can copy those and paste them down. "Farias" wrote: Hi Ted You may enter this formula in cell Sheet2 A2 =IF(Sheet1!$G$2="yes",Sheet1!A2,"") Then just copy it and paste it on B2:G2 Thanks "Ted Thomson" wrote: 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 (Monthly) 2. an induvidual stats sheet (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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Feed Data from Multiple worksheets to a Summary Worksheet | Excel Worksheet Functions | |||
| Copying Cells From Multiple Worksheets to Create Summary Sheet | Excel Discussion (Misc queries) | |||
| Creating a summary sheet from data across multiple worksheets | Excel Discussion (Misc queries) | |||
| create yearly summary from monthly worksheets | Excel Discussion (Misc queries) | |||
| How do I display data from multiple worksheets in a summary sheet. | Excel Discussion (Misc queries) | |||
Linear Mode
