I have an excel matrix workbook with 5 sheets and a roll up sheet.

In column A you put a ranking of +1 to +5 for each Criteria. Then in B, C, & D you’d do the same. I want a ranking scale of +1-+5 and I want to eliminate repeats. So if you enter +1 in A for Criteria 1 you can’t use +1 again in B, C &D for Criteria 1. It forces you to use only those numbers that are left. So B could be any of the remaining numbers +2-+5 but not +1 for Criteria 1. Once you select B (say +4) now C could only be +2, +3 & +5 for Criteria 1. Each Criteria would be set up the same with a ranking scale available +1-+5. So each row is independent.

The other challenge is that this would have to be done over the 5 sheets. So each Column A is a separate sheet with the 4 rows of Criteria and the rule or formula would have to work across the separate sheets. So on the Sheet for Column B it would have to know that for Criteria 1 +1 was used on sheet 1 (column A) and not available to select.

excel-matrix.xlsx