Linked cells...

  • Thread starter Thread starter Akomic
  • Start date Start date
A

Akomic

Help!

Here's what I want to do...

Sheet 1: Summary of Sheets 2 - 12.

Sheet 2 - 12: Set of assessment data for a class of children - each sheet a
different subject, but all sheets identical in every other way (except, of
course, for assessments recorded in the boxes).

The rough look of sheets 2 onwards is as follows:

Christian Name. Surname. Group/Set. Average Mark. Unit 1.
Unit 2...
Joe. Bloggs. 1 =AVERAGE(B5:B23) 2.
3.

So, as you can see, the 4th column shows the average score so far, based on
how many units have been completed.

On sheet 1, I want to create a summary for the class, so that is shows all
the average scores so far for all subjects (ie the 4th column from sheets
2-12)

It will look something like:

Christian Name. Surname. Maths. English. Science...


My question is, how can I set this up so that it will automatically provide
me with the correct information.

I want to type the names into sheet 1, but then have all the other sheets
referring to the typed names, so that IF another child joins my class during
the year, and I update sheet 1 (eg automatically by surname...), all the
other sheets will also include this updated list AND ORDER, maintaining the
relevant assessment data in the 'Unit' columns...

I can't decide if this will work, and if so, HOW to do it... any suggestions
greatly appreciated!!!

Ta loads,

Jon
 
If I’ve read your intents correctly, perhaps something
set-up along these lines would give you a good start ..

-------------------
In sheet: Sub1 (Sub1 = Subject 1)
-------------------
The headers listed across in A1:Z1 are:

ChristianName Surname Group/Set AverageMark Unit1 Unit2 ... Unit22

Put in A2: =Summary!A2
Copy A2 across to C2

Put in D2:
=IF(ISBLANK(E2:Z2),"",AVERAGE(IF(E2:Z2<>0,E2:Z2)))

Array-enter the formula
i.e. Press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Done correctly, Excel will wrap the formula with curly braces { }, viz.:
{=IF(ISBLANK(E2:Z2),"",AVERAGE(IF(E2:Z2<>0,E2:Z2)))}

Now select A2:D2 and copy down a “safe” number of rows say, down to D200
to cover the max likely number of names to be entered in Sheet: Summary
(The set-up in sheet: Summary is given later below)

Cols A to C will return the first 3 cols of data entered into sheet: Summary
(i.e. ChristianName Surname Group/Set)

Col D will compute the average mark of all the Units
(Unit1-Unit22) for Sub1 which are entered in cols E to Z

22 "Units" for a subject is probably excessive <g>
You can choose to adjust the range “E2:Z2”
where it occurs in the formula in D2, to a smaller range,
say: “E2:I2” if the max units per subject is only 5,
or you can also just leave it as it is
(Unused cols will not affect the Average)

For a neater look, we’ll suppress extraneous zeros
from showing in this sheet: Sub1 via:
Tools > Options > View tab > Uncheck "Zero values" > OK

With the set-up done for sheet: Sub1,
make* another 11 copies of this sheet
and rename them: Sub2, Sub3... Sub12

*via e.g.: Right-click on the sheet tab > Move or Copy
Check “Create a copy” > OK
Click to expand...

------------
In Sheet: Summary
------------
The headers listed in A1:O1 are:

ChristianName Surname Group/Set Sub1 Sub2 ... Sub12

Cols A to C will be for manual input.
Data entered in cols A to C in this sheet
will be auto-populated in each of the 12 subject sheets

The average marks for each of the 12 subjects (Sub1 .. Sub12)
will be extracted from col D in each of subject sheets
and returned by the formula below

Note that the subject names: Sub1, Sub2 ... Sub12 entered in D1:O1
must *match* the actual sheetnames used for the subjects
(if/when you change the arbitrary names (Sub1, Sub2 ..) to actual ones)

Put in D2:

=IF(ISERROR(INDIRECT("'"&D$1&"'!D"&ROW(A2))),"",IF(INDIRECT("'"&D$1&"'!D"&RO
W(A2))=0,"",INDIRECT("'"&D$1&"'!D"&ROW(A2))))

Copy D2 across to O2, then down as many rows as there is data

--
If you’d like to have a sample book for the above construct,
just post a “readable” email in response here
I’ll send it to you via private email
 
Back
Top