Solved

Excel Matrix

Posted on 2014-01-15
13
385 Views
Last Modified: 2014-01-20
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
0
Comment
Question by:mcthomas00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39784424
See if this meets your requirements. It uses Data Validation. Starting on Sheet1, G2, this formula is used:

=(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
0
 

Author Comment

by:mcthomas00
ID: 39785282
Where would I enter the above formula
0
 

Author Comment

by:mcthomas00
ID: 39785308
I re-read the answer, I would enter the formula in the Data Validation.  
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.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:mcthomas00
ID: 39785355
Also noticed after further experimenting.  The formula does check to see if the value is on one of the 5 sheets, but it is checking to see if the value has been added to the  same cell on the previous sheets.  I need it to check all 5 sheets an any of the cells for that criteria no matter which cell the value was entered
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39786152
If I'm understanding you correctly, you want the numbers 1-5 to appear only once among the 5 sheets. So if Sheet1 has 1,2,3,4 in A-D, 5 can only appear on 1 of the remaining sheets. If that's the case, this formula should work:

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

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

Author Comment

by:mcthomas00
ID: 39786217
1.  So on sheet 1 replace the formula in all cells to the above with the exception of changing the cell reference.

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

Accepted Solution

by:
Flyster earned 500 total points
ID: 39786295
1. Yes. If you place this formula in A1, you can copy and paste it to the other cells. The reference will change automatically.

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)=0) * (COUNTIF(Sheet2!$G2:$J2,G2)=0) * (COUNTIF(Sheet4!$G2:$J2,G2)=0) * (COUNTIF(Sheet5!$G2:$J2,G2)=0)

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!!
0
 

Author Comment

by:mcthomas00
ID: 39786679
Thank you it works!  Can you explain what the formula is telling me.  I understand the >0 and <6 as the results within that range.
0
 

Author Closing Comment

by:mcthomas00
ID: 39786837
Awesome work!
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39786869
=(G2>=1) * (G2<6) : This assures that only 1 through 5 is entered. As I mentioned above, the cells are formatted as numbers with no decimal places, so if someone entered 5.9 it would show up as a 5, and is still less than 6. The >=1 means that 1 is the lowest value allowed.

(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)=0) : This looks at sheet2, $G2:$J2 and counts the number of times the above value (1) appears. If there's a 1 here, it would mean the count is 1 and therfore violates the "=0" rule. The same is for the other three sheets.

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

Author Comment

by:mcthomas00
ID: 39788521
Thank you for the explanation.  One more question, you used * to separate.  Reason
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39793340
The multiply operator (*) is used to get numeric value of the criteria. Data validation is met when there's a value of 1 (True). If you have three criteria's and they're all true, then you have 1*1*1 which equals 1. If one of them was 0 (False) then the total result would be 0. 1*0*1 equals 0.
0
 

Author Comment

by:mcthomas00
ID: 39794090
THank you
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question