create an "if then" formula

[Ms. D]

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".

 

[Max]

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,INDIRECT("'"&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,INDEX(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]

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]

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
---

 

[Ms. D]

Thanks, I getting it but one problem

Here's my formula

=IF(Jan_PL_complete_all_classes!A3:A108=Jan_PL_complete_all_classes!A3:A139,Jan_PL_complete_all_classes!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]

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]

Ok - I'm a slow learner

In Sheet 7 is where the formula needs to be the data is in Wks 1-6 but I'm
doing one Wks at a time

Wks 1 looks like this

A B C D
Atlanta Chicago New York
XX 1234 9202 333
YY 4930 331 2893
ZZ 393 3930 3030

Wks 2 looks like this

A B C D
Atlanta Chicago New York
XX 6789 3456 333
AA 2030 39 90
BB 796 989 5930
ZZ 806 7777 3030

Wks 7 has the results

Wks 1 Wks 2
so on ...
A B C D E
Atlanta Atlanta Chicago Chicago
XX 1234 6789 9202 3456
AA 0 2030 0 39

YY 4930 0 331 0
BB 0 796 0 989

ZZ 393 806 3930 7777

I'm formatting Wks 7 one column at a time basis on the previous Wks 1-6

Thanks in advance for your help!!!!!!

 

[Max]

Ahh, I just realized you posted in .worksheet.functions as well. Please
don't multi-post. You've got a response there from Herbert with a link to a
sample book. You should follow through & feedback to him there.

Over here, well .. think I've already given your post/issue whatever help I
can.

(You don't have to press the "Yes" button to this response,
since you seem to have an aversion to clicking that button)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik

 

[Ms. D]

SOOOOOOOOOO CLOSE TO A SOLUTION - I DID NOT UNDERSTAND
HERBERT'S RESPONSE - PLEASE HELP ME!

 

[Max]

.. Soooooooooo close to a solution ..

Taking the set-up from your other post in .worksheet.functions
here's my thoughts ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3k364
Summarizing 6 sheets.xls

In Total,

Put in C3
=SUMPRODUCT((INDIRECT("'"&C$1&"'!A2:A200")=$A3)*(INDIRECT("'"&C$1&"'!B2:B200")=$B3)*OFFSET(INDIRECT("'"&C$1&"'!B2:B200"),,MATCH(C$2,INDIRECT("'"&C$1&"'!1:1"),0)-2,))
Copy C3 across to H3, fill down as far as required. Adapt the ranges to suit.

Notes: As detailed in the sample, a complete listing of codes & descriptions
is assumed in A3:B3 down. Sheetnames are listed in C1:H1, cities in C2:H2
like this:

Jan Feb Jan Feb Jan Feb
Atlanta Atlanta Chicago Chicago Detroit Detroit

I did not understand Herbert's response

All the more reason that you should feedback to him over there

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---