T
tedt
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
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