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?

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),"")
--
Regards
Roger Govier

"Sandypants" wrote in message
...
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?

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

"Roger Govier" wrote:

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),"")
--
Regards
Roger Govier

"Sandypants" wrote in message
...
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?

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),"")
--
Regards
Roger Govier

"Sandypants" wrote in message
...
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

"Roger Govier" wrote:

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),"")
--
Regards
Roger Govier

"Sandypants" wrote in message
...
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?

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

Warm Regs

Sandypants :0)

"Roger Govier" wrote:

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),"")
--
Regards
Roger Govier

"Sandypants" wrote in message
...
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

"Roger Govier" wrote:

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),"")
--
Regards
Roger Govier

"Sandypants" wrote in message
...
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?

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

On Oct 6, 12:43*pm, Sandypants
wrote:
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



"Roger Govier" wrote:
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),"")
--
Regards
Roger Govier

"Sandypants" wrote in message
...
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?- Hide quoted text -

- Show quoted text -