Vlookup vs IF across several sheets

[wattzz2000]

Search and count.
I want to search 80 sheets for a certain number (like 93309) in $L$9:$L:$L62 and if it is true then I want it to return a "text" statement from $B$144:$B$374.

Then I want to do the same thing with a SUM formula.

How can this be accomplished?

 

[Aladin Akyurek]

Does 93309 occur just once across 80 sheets? Or does picking up the first occurrence suffice?

 

[wattzz2000]

It may or may not occur multiple times and there are hundreds of codes. This is for a Bill of Materials in an estimating program.

 

[Aladin Akyurek]

It may or may not occur multiple times and there are hundreds of codes. This is for a Bill of Materials in an estimating program.

So you want all of the text statements when the code occurs say 30 of those sheets?

By the way, where is the sum range?

 

[wattzz2000]

SELECT MATERIAL......Qty....UM...Total Units....Unit Cost....Total Cost...Cost Each......KEYEDIN CODE
ALUMINUM_SHEET ....6.00...SF.......6.00............4.00...........24.00.........24.00..............93309

*periods to force spacing

This is what it would look like on a take-off sheet.
I want to write a code that goes to each page of te 80 pages and looks for the KEYEDIN code value, and if it is there then it will report the total sum of all the units and the costs on a Bill of Materials Page that looks like this:

Total of All Materials
Description.................UM......Total Units.......Unit Cost.....Total Cost........KEYEDIN


Is there a way to post or send you the excel spreadsheet to look at?

It seems like an IF statement with a lookup, but I am having no success getting it to search for the values and then add them up.
I hope you can help and thank you for helping.

-JW

 

[Aladin Akyurek]

Create a range which houses the names of the relevant sheets. Select this range and name the selection SheetList.

Let $L$9:$L:$L62 house the KEYEDIN CODE and $K$9:$K$62 the total cost you want to sum.

Let also E2 houses a KEYEDIN CODE of interest like 93309.

Invoke for this summing task:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!K9:K62"),INDIRECT("'"&SheetList&"'!L9:L62"),$E2))

If you want to have the description from A2:A62 that corresponds to an KEYEDIN CODE of interest located in E2...

Control+shift+enter, not just enter:

=INDEX(INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!L9:L62"),$D2)>0,0))&"'!A9:A62"),MATCH(D2,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!L9:L62"),$E2)>0,0))&"'!L9:L62"),0))

Hope you can adapt and implement in your workbook.

 

[wattzz2000]

Aladin:

I am unfamiliar with the implementation of SheetList. How do I setup a range of sheets 1:80 (they are numbered sheets 1 through 80).

What is E2 referencing?

I want to calculate H9:H62, I9:I62, J9:J62, and K9:K62 from each sheet and ave those quantities on the BoM sheet.

In case you hadn't seen, I private messaged you the actual spreadsheet.

-Jonathan

 

[Aladin Akyurek]

Aladin:

I am unfamiliar with the implementation of SheetList. How do I setup a range of sheets 1:80 (they are numbered sheets 1 through 80).

Already told how. On a separate sheet enter 1 in A1 and 2 in A2, select A1:A2, and copy down till you get 80. Select the range from A2:A81, type SheetList in the Name Box on the Formula Bar. You are done.

What is E2 referencing?

As I already told, you enter in E2 a KEYEDIN CODE you are interested in.

[/quote]I want to calculate H9:H62, I9:I62, J9:J62, and K9:K62 from each sheet and ave those quantities on the BoM sheet.[/quote]

You need to run the SUMPRODUCT formula where you adjust for the range to sum within the SUMIFS bit.

In case you hadn't seen, I private messaged you the actual spreadsheet.{/quote]

I'm sorry Jonathan I don't want the whole spreadsheet. I hope you can do the adapting/adjusting yourself.

 

[wattzz2000]

Aladin:
I figured it out and it works famously. Thank you kindly!

 

[Aladin Akyurek]

Aladin:
I figured it out and it works famously. Thank you kindly!

Great to hear that. You are welcome.