Help needed with school records spreadsheet.

  • Thread starter Thread starter Chris Mitchell
  • Start date Start date
C

Chris Mitchell

Please see
http://www.chris.a.mitchell.btinternet.co.uk/FoundationStageTemp.xls



This is a complex (well it is for me) spreadsheet that I'm trying to develop
for the other half who as you've probably worked out if you've looked at the
file is a teacher. She has to keep records and produce reports of the
children's development progress in a number of areas. Next year she goes
from being responsible for 32 to 64 kids, and to my mind the current paper
records will become a major pain.



With help from these groups I have managed to cobble together the formulae
[=SUMPRODUCT(COUNTIF(INDIRECT({"E3","I3","Q3","U3","Y3","AA3"}),"/"))] in
cell AC:1 of the PSE sheet to sum the number of "/"s in the Entry column of
the various sub tasks. Problem is this formulae won't adjust when I drag it
to AD:1 and AE:1.



Also I will need a similar formulae for the many other Sub-totals throughout
the spreadsheet.



Anyone got any ideas as to a suitable formulae that would do the trick, and
that would be more amenable to coping and pasting. Coding it by hand each
time would be a major pain.



Would I be better of with a MS Access database? I know less about Access
than I do about Excel, so this would be a steeper learning curve for me.



Better yet are there any teachers out there who have already done this and
that are prepared to share their labours with me, saving me having to
re-invent the wheel? I'll update the web based copy from time to time as I
progress this, so if you've got a similar problem/requirement feel free to
comment/nick copies as the need arises.



TIA
 
Hi
try:
=SUMPRODUCT(COUNTIF(INDIRECT({cell("address",E3),cell("address",I3),cel
l("address",Q3),cell("address",U3),cell("address",Y3),cell("address",AA
3)}),"/"))]


But as I haven't open your file (as most people here won't do) I'm not
sure if you can't change the layout of your file.<the data of interest
seems to be a little bit scattered in your file :-)
 
Please see
http://www.chris.a.mitchell.btinternet.co.uk/FoundationStageTemp.xls



This is a complex (well it is for me) spreadsheet that I'm trying to
develop for the other half who as you've probably worked out if you've
looked at the file is a teacher. She has to keep records and produce
reports of the children's development progress in a number of areas.
Next year she goes from being responsible for 32 to 64 kids, and to my
mind the current paper records will become a major pain.



With help from these groups I have managed to cobble together the
formulae
[=SUMPRODUCT(COUNTIF(INDIRECT({"E3","I3","Q3","U3","Y3","AA3"}),"/"))]
in cell AC:1 of the PSE sheet to sum the number of "/"s in the Entry
column of the various sub tasks. Problem is this formulae won't adjust
when I drag it to AD:1 and AE:1.
Click to expand...

=SUM(IF(E3="/";1;0);IF(I3="/";1;0);IF(Q3="/";1;0);IF(U3="/";1;0);IF(Y3="/";1;0);IF(AA3="/";1;0))

Chris,

I used OpenOffice.org calc to import your sheet, the only difference
between this and Excel is the use of ; instead of commas. (And the fact
the software is free) The above is maybe a little less elegant than your
original formula but your original gave me an error I couldn't see the
reason for so I went to something simpler that could be built up a bit at
a time. Seems to work and copy. Its easy to enter by just copying the
first IF(E3="/";1;0) casting it several times then just adjust the
references, then copy to other cells works in the same way.

Hope this helps. If not try uk.education.schools-it or the OpenOffice.org
users list. (e-mail address removed)
 
Thanks.

I've used a variation of this and it copies and pastes and drags exactly as
I want.

Ian said:
Please see
http://www.chris.a.mitchell.btinternet.co.uk/FoundationStageTemp.xls



This is a complex (well it is for me) spreadsheet that I'm trying to
develop for the other half who as you've probably worked out if you've
looked at the file is a teacher. She has to keep records and produce
reports of the children's development progress in a number of areas.
Next year she goes from being responsible for 32 to 64 kids, and to my
mind the current paper records will become a major pain.



With help from these groups I have managed to cobble together the
formulae
[=SUMPRODUCT(COUNTIF(INDIRECT({"E3","I3","Q3","U3","Y3","AA3"}),"/"))]
in cell AC:1 of the PSE sheet to sum the number of "/"s in the Entry
column of the various sub tasks. Problem is this formulae won't adjust
when I drag it to AD:1 and AE:1.
Click to expand...
=SUM(IF(E3="/";1;0);IF(I3="/";1;0);IF(Q3="/";1;0);IF(U3="/";1;0);IF(Y3="/";1
;0);IF(AA3="/";1;0))

Chris,

I used OpenOffice.org calc to import your sheet, the only difference
between this and Excel is the use of ; instead of commas. (And the fact
the software is free) The above is maybe a little less elegant than your
original formula but your original gave me an error I couldn't see the
reason for so I went to something simpler that could be built up a bit at
a time. Seems to work and copy. Its easy to enter by just copying the
first IF(E3="/";1;0) casting it several times then just adjust the
references, then copy to other cells works in the same way.

Hope this helps. If not try uk.education.schools-it or the OpenOffice.org
users list. (e-mail address removed)

--
IanL
ZMS Ltd - Education Management Consultants
WWW.thelearningmachine.co.uk
WWW.theINGOTs.org
Click to expand...
 
Back
Top