Issues with Linking Data in Various Tabs within a workbook?

[Sandypants]

Can anyone help please with the following:
I have a very large workbook with pupils names and abilities listed on 10
tabs all listed alphabetically and identical. Alongside this data I also have
individual numerical and written data for each pupil under their different
subjects.
i.e. Lucy Smith, Female, Able + Talented, Class 11A, Teacher N Jones, KS3-C,
Current Grade D, etc then Subject data for each pupil, i.e English, Maths etc

I have tried to link the data to match only the first bit, i.e the generic
name and abilities area onto numerous tabs, however it eiher links the data
fine but adds in '0' into blank boxes, which have to remain blank, and when I
choose 'skip blanks' it simply loses all of the formatting!
The other issue is that if I delete a pupils entire row from the first
sheet, will it know to delete the same 'row' from the other tabs and delete
only the same 'row'?
If i link the entire sheet to the other tabs wont it mean that it copies the
whole data? I only need it to copy certain cells info but somehow get the
sheet to know that if i insert a row or delete a row then it has to do the
same in the other tabs for the same pupil only?
Help please?

 

[Roger Govier]

Hi

Assuming each of your sheets has no space in its name, e.g. Sheet1 not Sheet
1, and that row 1 of each sheet contains the category headings, and student
name is in column A.

Then, in on your Summary Sheet you had Names in column A, starting with cell
A3 and had the Sheet names you wanted the data from in row 1 starting with
B1, and Category type in row 2 starting with B2, enter the following formula
in Summary sheet cell B3 and copy across and down as required.

=IF(COUNTIF(INDIRECT(B$1&"!A:A"),$A3),
VLOOKUP($A3,INDIRECT(B$1&"!A:Z"),
MATCH(B$2,INDIRECT(B$1&"!A1:Z1"),0),0),"")

 

[Sandypants]

Hi Roger,
Before I attempt this can i ask what happens if my sheets are named with
spaces in them please? I have numerous tabs and they do all have names with
spaces so that I can read them and differentiate between each one im afraid?

Thanks again
Sandypants

 

[Pete_UK]

If you want to reference a sheet which has spaces in the name you have
to include apostrophes around the sheet name, so you will have to
amend Roger's formula like this:

=IF(COUNTIF(INDIRECT("'"&B$1&"'!A:A"),$A3),
VLOOKUP($A3,INDIRECT("'"&B$1&"'!A:Z"),
MATCH(B$2,INDIRECT("'"&B$1&"'!A1:Z1"),0),0),"")

Note there is an apostrophe before the ! as well at the beginning of
each INDIRECT between the "".

Hope this helps.

Pete

 

[Roger Govier]

Hi

The only difference is the formula is a bit longer.
the Indirect part of the formula, where there are spaces in the sheet name,
requires the sheet name to be enclosed in single quotes
'My special sheet'
In order to do this in side the indirect function, we would need
=INDIRECT( " ' " & B1 & " ' ! A:A " )

I have deliberately spaced out the characters so you can see the single
quote enclosed between the first pair of double quotes, and before the
Exclamation mark.

=IF(COUNTIF(INDIRECT("'"&B$1&"'!A:A"),$A3),
VLOOKUP($A3,INDIRECT("'"&B$1&"'!A:Z"),
MATCH(B$2,INDIRECT("'"&B$1&"'!A1:Z1"),0),0),"")

 

[Sandypants]

Thanks Pete and Roger - I will give it a go!

Warm Regs

Sandypants :0)