Thread: Lookup multiple cell values throughout several sheets in a workbook

Hello,

I have been struggling with this for over 2 days, searching forums, trying several formula's, and i am at my wits end.

I am trying to summerize on the Master BMO List, the number of lengths required for each of the listed materials that are throughout the individual BMO Sheets in the workbook. In the attached samplei only show one BMO Sheet, but in my workbook, I have close to 30.

Master BMO List (Sheet 1 Name)
Column A - Lists the material name
Column B - is where i want the lookup data to appear, next to the appropriate material name

BMO SS Sloped 2 Bar (Sheet 2 Name)
Column B is the data that has to be matched against Column A in Master BMO List
Column N is the value of the response I am looking for.

As you can see, there may be several rows that contain the matched name, giving multiple values, which i would like combined to give a running total.

eg. SS-316 1-1/4" PIPE is in 3 different rows in the individual BMO Sheet. I need to calculate the corresponding N column value for that same row that has that material. The formula would go in B138 in Master BMO.


Your comments are greatly appreciated.

Did you have a look at SUMIF?

e.g, in B4:

=SUMIF('BMO - SS SLOPED 2 BAR'!B:B,A4,'BMO - SS SLOPED 2 BAR'!N:N)

copied down.

If you need to refer to a specific sheet for each entry, then introduce a new column in the summary sheet and in corresponding cells insert the sheet name... then in the # of lengths required column use formula like:

=SUMIF(INDIRECT("'"&B4&"'!B:B"),A4,INDIRECT("'"&B4&"'!N:N"))