Find a value in multiple sheets and compare it to the qty in the next column
I'm trying to create a formula in the column [Purchased Parts Available?] that uses the [Job #]@row field in, looks for it in 15 other sheets (Purchased Parts 1.....Purchased Parts 15) and sees if the Qty Available in the those sheets is greater than 0. If all locations found with the [Job #]@row value have a Qty greater than 0, then return a value of "1", else deliver a "0". I've tried COUNTIF(S) with VLOOKUP, but can't use VLOOKUP because it only returns the first value found. See example below.
Answers

I think you can accomplish this with a series of simple COUNTIF statements added together for one column, let's call it "Total Parts":
= COUNTIF( {Purchased Parts 1Job #} , [Job #]@row ) + COUNTIF ( {Purchased Parts 2Job #} , [Job #]@row ) + ...etc
Then use an IF formula in the [Purchased Parts Available?] column to see if that total count is greater than 0. I'm assuming that it's a checkbox column.
= IF ( [Total Parts]@row>0, true )
or = IF ([Total Parts]@row>0, "Yes" ) if it's a text column or picklist.
Note that you cannot mash together a COUNTIFS or SUMIFS statement that looks across multiple sheets, as those functions require that the range is the same size for each of the criteria. In other words, you can't do = COUNTIFS ({Sheet 1},Job@row, {Sheet 2},Job@row, etc) because the Sheet 1 and Sheet 2 ranges are different.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 377 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!