Compound if statement?

graphpak

New Member
Joined
Jan 24, 2005
Messages
2
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

graphpak

New Member
Joined
Jan 24, 2005
Messages
2
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

graphpak said:
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

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! :LOL:

This what you're looking for?
Dan
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Todd Bardoni said:
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.
 

Forum statistics

Threads
1,148,269
Messages
5,745,776
Members
423,971
Latest member
Jogesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top