Compound if statement?

[graphpak]

Here is my situation:

I am working with 3 worksheets...each worksheet represents a location of a warehouse. There are 2 columns in each worksheet, one an item# and the next a quantity. What I'm trying to do is create a formula or possibly a macro that would allow me to enter the item# and excel would lookup every instance of that item# at all 3 locations and then calculate a total number of pieces from the 3 locations. Can someone please help me with this formula! THANKS!!

I am having trouble with Aladin's suggestion...is anyone willing to enter this formula for me if I send you an email with my excel file attached to it?

 

[Aladin Akyurek]

See:

http://www.mrexcel.com/board2/viewtopic.php?t=123314&highlight=sheetlist

 

[graphpak]

OK, so I've been told that what I'm looking for is called a dynamic named range...but i haven't the slightest clue as to how to set it up...Aladin I hear your the expert...can you please help me?

 

[Zack Barresse]

Edit: See Aladin's solution.

 

[Aladin Akyurek]

OK, so I've been told that what I'm looking for is called a dynamic named range...but i haven't the slightest clue as to how to set it up...Aladin I hear your the expert...can you please help me?

Is the set up described in the quoted link not adaptable to your problem?

 

[tactps]

Although Aladin is a far better exceller than I, it sounds to me that a sumif formula will work:

In A1, type the item number
In B1, type:
=sumif(range_item_sheet1,A1,range_quantity_sheet1)+sumif(range_item_sheet2,A1,range_quantity_sheet2)+sumif(range_item_sheet3,A1,range_quantity_sheet3)

e.g.
=SUMIF([Book5]Sheet1!$A:$A,A1,[Book5]Sheet1!$B:$B)+=SUMIF([Book6]Sheet1!$A:$A,A1,[Book6]Sheet1!$B:$B)+=SUMIF([Book7]Sheet1!$A:$A,A1,[Book7]Sheet1!$B:$B)

 

[HalfAce]

In response to your PM, here's what I came up with...
Here's an example of how you can do this with a VLOOKUP formula.
On the first (location) sheet, I named columns A & B (the entire cloumns) Location1
On the second sheet I named them Location2
The third sheet... Location3

Then, in all 3 sheets, in cell E1 I entered this formula:
=IF(ISERROR(D1=VLOOKUP(D1,Location1,2,0)+VLOOKUP(D1,Location2,2,0)+VLOOKUP(D1,Location3,2,0)),"Item not found in at least one sheet.",IF(D1="","",VLOOKUP(D1,Location1,2,0)+VLOOKUP(D1,Location2,2,0)+VLOOKUP(D1,Location3,2,0)))

Now, whatever item number I enter into D1 (in any of the 3 sheets), the total quanity of that item from all 3 sheets returns in E1. If the item is missing from one or more of the sheets, you get a message telling you so.

We can do it with VBA too, but this formula seems to be working fine for me.

(I don't know which of all the formulas offered would be more efficient, but with my understanding of formulas, it probably ain't gonna be mine!

This what you're looking for?
Dan

 

[Brian from Maui]

First you need to Define your worksheets. Go to Insert/Name/Define, in the Names in Workbook, enter SheetList and in the refers to window enter,

={"sheet1","sheet2","sheet3"} ..... sub your real sheet names for sheet 1 etc.

Now enter in your summary sheet,

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),A1,INDIRECT("'"&SheetList&"'!B2:B10")))

Where A2:A10 houses the part numbers, A1 houses the part number you're searching for and B2:B10 the range to sum.

 

[Todd Bardoni]

Wow! Brian, that is a fantastic solution! I've never thought of naming an array like that before. Fantastic!

Edit:- Must mention that the formula credit goes to Aladin of course...but I love the sheet name array as a defined name!

 

[Brian from Maui]

Wow! Brian, that is a fantastic solution! I've never thought of naming an array like that before. Fantastic!

Hey Todd,

It ain't me...it's Aladin. I use something similar, but not for summing.