Using a formula with multiple sheets

[excelstumped2]

I have a spreadsheet with 10 sheets which will be used for keeping track of jersey number inventory. On the first page I have a table with rows ranging from XS to XL and each cell next to each range has a number representing a jersey number. On the other sheets, which are for each team, I have the same table.

What I want to happen is when I enter a number into a cell for a specific jersey size, I want that number to be removed from the first page. This way I will always know what numbers are available.

I am using the following formula which works for one sheet only:
=IF(ISNA(MATCH(C33,Team3!L4:AM4,0)),C33,"")

On my first page, I have the jersey number table twice. The bottom table is the main copy that wont change. The top table is where I am putting the formulas in and is referencing the bottom table. So in the above formula, C33 is in the bottom table. This formula works exactly how I want it but only for one page. If I go to the Team3 sheet and enter the C33 number, which is 3 in this case, the cell is blank. If I delete that number from the row in sheet Team3, the 3 appears.

I have tried doing multiple sheets but the formula doesnt work. I have tried =IF(ISNA(MATCH(C33,Team3:Team4!L4:AM4,0)),C33,"") and different variations. Please assist in any way. Thanks.

 

[Domenic]

First, list your sheet names in a range of cells, let's say G2:G10. Then try...

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$2:$G$10&"'!L4:AM4"),C33))=0,C33,"")

Hope this helps!

 

[excelstumped2]

Domenic,

That is exactly what I was looking for. I have been trying to figure it out for weeks. Thanks for your help and expertise!

 

[Domenic]

You're very welcome. Glad I could help.

Cheers!