I have 6 worksheets which I would like to copy the data into a "summary"
worksheet. I would like to make this as simple as possible, so how can I
copy or create a formula in the "summary" worksheet that will check the
legend in column "a" and return the value with that value that in column "b"
if the "summary" worksheet column "a" does not have a value in the previous 6
worksheets return the value of "0".

One play ..

In your summary sheet,
With lookup values assumed in A2 down

List* the 6 sheetnames across in C1:H1
Ensure that these names match exactly (except for case) with what's on the
tabs

Put in C2:
=INDEX(INDIRECT("'"&C$1&"'!B:B"),MATCH($A2,INDIREC T("'"&C$1&"'!A:A"),0))
Copy C2 across to G2

Put in B2, then array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNA(MATCH(TRUE,NOT(ISERROR(C2:H2)),0)),0,INDE X(C2:H2,MATCH(TRUE,NOT(ISERROR(C2:H2)),0)))

Select B2:H2, fill down to cover the max expected extent of lookups in col A
Col B will return the required results

*Note that the left-to-right sequence in which you list the names in C1:H1
will determine the results that's returned in col B. In the event of multiple
matches within any of the 6 sheets & across the 6 sheets, only the 1st
matched instance (from top down/left-to-right) will ultimately appear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Ms. D" wrote:
I have 6 worksheets which I would like to copy the data into a "summary"
worksheet. I would like to make this as simple as possible, so how can I
copy or create a formula in the "summary" worksheet that will check the
legend in column "a" and return the value with that value that in column "b"
if the "summary" worksheet column "a" does not have a value in the previous 6
worksheets return the value of "0".

Thanks - too hard to understand - can you make it a simple

if wks 1 column "A" = column "A" in wks 7 then enter amt / else 0

"Max" wrote:

One play ..

In your summary sheet,
With lookup values assumed in A2 down

List* the 6 sheetnames across in C1:H1
Ensure that these names match exactly (except for case) with what's on the
tabs

Put in C2:
=INDEX(INDIRECT("'"&C$1&"'!B:B"),MATCH($A2,INDIREC T("'"&C$1&"'!A:A"),0))
Copy C2 across to G2

Put in B2, then array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNA(MATCH(TRUE,NOT(ISERROR(C2:H2)),0)),0,INDE X(C2:H2,MATCH(TRUE,NOT(ISERROR(C2:H2)),0)))

Select B2:H2, fill down to cover the max expected extent of lookups in col A
Col B will return the required results

*Note that the left-to-right sequence in which you list the names in C1:H1
will determine the results that's returned in col B. In the event of multiple
matches within any of the 6 sheets & across the 6 sheets, only the 1st
matched instance (from top down/left-to-right) will ultimately appear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Ms. D" wrote:
I have 6 worksheets which I would like to copy the data into a "summary"
worksheet. I would like to make this as simple as possible, so how can I
copy or create a formula in the "summary" worksheet that will check the
legend in column "a" and return the value with that value that in column "b"
if the "summary" worksheet column "a" does not have a value in the previous 6
worksheets return the value of "0".

"Ms. D" wrote:
Thanks - too hard to understand - can you make it a simple

Ahh, thought I had read your original posting quite okay. That you had that
kind of underlying complex scenario to handle. What was suggested earlier was
really a simple way to tackle that scenario.

Ok, as for my reading on this ..
if wks 1 column "A" = column "A" in wks 7 then enter amt / else 0

I'll half-assume the "amt" is in col B (I went back to your original posting)

You could enter this in say C2: =IF(A2=Sheet7!A2,B2,0)

And if the above did help you in any way to progress on whatever it is that
you're trying to do, do take a moment to press the "Yes" button below ...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---

Thanks, I getting it but one problem

Here's my formula

=IF(Jan_PL_complete_all_classes!A3:A108=Jan_PL_com plete_all_classes!A3:A139,Jan_PL_complete_all_clas ses!B3,0)

The only thing wks 1 column A "wording" if it matches wks 7 column A
"wording" then value in column B that matches that column

Example

Wks 1

XX 1234
YY 4930
ZZ 393

Wks 7

XX 1234
AA 0
YY 4930
ZZ 393
BB 0

"Max" wrote:

"Ms. D" wrote:
Thanks - too hard to understand - can you make it a simple

Ahh, thought I had read your original posting quite okay. That you had that
kind of underlying complex scenario to handle. What was suggested earlier was
really a simple way to tackle that scenario.

Ok, as for my reading on this ..
if wks 1 column "A" = column "A" in wks 7 then enter amt / else 0

I'll half-assume the "amt" is in col B (I went back to your original posting)

You could enter this in say C2: =IF(A2=Sheet7!A2,B2,0)

And if the above did help you in any way to progress on whatever it is that
you're trying to do, do take a moment to press the "Yes" button below ...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---

One last guess, maybe this is what you're after

In Sheet1,
Put in C2: =IF(INDEX(Sheet7!B:B,MATCH(A2,Sheet7!A:A,0))=B2,B2 ,0)
Copy down to the last row of data in col A

Take a moment to press the "Yes" button below ...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Ms. D" wrote:
Thanks, I getting it but one problem

Here's my formula

=IF(Jan_PL_complete_all_classes!A3:A108=Jan_PL_com plete_all_classes!A3:A139,Jan_PL_complete_all_clas ses!B3,0)

The only thing wks 1 column A "wording" if it matches wks 7 column A
"wording" then value in column B that matches that column

Example

Wks 1

XX 1234
YY 4930
ZZ 393

Wks 7

XX 1234
AA 0
YY 4930
ZZ 393
BB 0