This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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

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

=(G2>0) * (G2<6) * (COUNTIF($G2:$J2,G2)=1) * (G2<>Sheet2!G2) * (G2<>Sheet3!G2) * (G2<>Sheet4!G2) * (G2<>Sheet5!G2)

It allows only numbers greater than 0 and less than 6. The cells are formatted as numbers with zero decimal places, in case someone wants to enter something like 1.4. In that case, it will just appear as a 1. The formula then checks the other 4 sheets to see if that number exists elswhere. If it does, an error message will appear prompting for a different number.

Flyster

excel-matrix.xlsx

I tested entering information in the cells. The formula is only checking duplicate cells on the same sheet, not across the 5 sheets. Ex. I can enter the #5 on sheet 1, but cannot enter #5 on sheet one again, but I can enter the #5 on another sheet. I need the error to appear if that value appear in Criteria 1 on all 5 sheets.

=(G2>0) * (G2<6) * (COUNTIF($G2:$J2,G2)=1) * (COUNTIF(Sheet2!$G2:$J2,G2

Note: This formula is for Sheet1. For Sheet2 you would change "(COUNTIF(Sheet2!$G2:$J2,G

2. For sheet to you said to change only where it reference Sheet 2. What would sheet 3, 4 and 5 be changed to.

(COUNTIF($G2:$J2,G2)=1) : It takes the range, in this case $G2:$J2 and looks for the count value, the value in G2. When you first enter a number into the range, say 1, COUNTIF counts the number of times 1 appears. So for the first time the count would be 1. If you try to enter it again, in the same range, the count would be 2, which does not equal 1 which violates the validation rule.

(COUNTIF(Sheet2!$G2:$J2,G2

Let me know if you have any other questions. I'm glad to help! :)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

2. The last 4 COUNTIF statements will always refer to the other sheets, not the sheet you're working in. So on Sheet3 you would have:

=(G2>0) * (G2<6) * (COUNTIF($G2:$J2,G2)=1) * (COUNTIF(Sheet1!$G2:$J2,G2

Also, 1 small tweak. Change the first part from (G2>0) to (G2>=1). This will prevent someone from entering something like .5 and getting away with it!!