Where used Parts List

[fatboy2131]

Hello Everyone,

I've created a spreadsheet which has tabs at the bottom of the sheet and each tab has a certain amount of models and parts. The format is like this:

Model a (tab name)

Column A = Part Number
Column B = Description
Column C = Model 1a
Column D = Model 2a
Column E = Model 3a

I list all part numbers in cell A4-A?, same with descriptions in cell b4.

In cell C4-E4 I place an "X" if the part in the cell A4 is used on the model listed in C4, D4 or E4 respectively.

I have this same set up across 12 different tabs (model a - l) Each tab may have anywhere from 10-50 different part numbers, and up to 8 different model variations (model 1a, 2a, 3a, 4a, 5a, etc). Some part numbers are used across more than one tab.

Now, in my first tab which I call combined parts list, I want to have all the part numbers from all the lists (which I've done manually already) as well as every model, so it's set up like this:

Column A = Part Number
Column B = Description
Column C = Model 1a
Column D = Model 2a
Column E = Model 3a
Column F = Model 1b
Column G = Model 2b
Column H = Model 3b
Column I = Model 1c
Column J = Model 2c
Column K = Model 3c

etc, etc, etc...

Is there a formula, that I can use starting on C2 that will search in the tabs of this workbook, so that if the part # in column A is used on the model listed in column C from that particular tab, then it will return either an X or a 1 in that cell.

Again, each model family (a, b, c, etc) might have more than one version (1a, 2a, 3a, etc). Each version of each model is listed in Row 1, starting with column C of each tab. So, I might have to do one for all 12 model families, which is fine, I just don't want to have to go through it all manually.