Home |
Search |
Today's Posts |
#1
|
|||
|
|||
making used values fall from a list
I am using 3 columns of data. The first is my original list. the second contains this =IF(COUNTIF(Bullets!$A$1:$K$310,A1)=1,"",ROW()) the third contains this =IF(ROW(A1:A85)-ROW(A1)+1COUNT(B1:B85),"",INDEX(A:A,SMALL(B1:B85, ROW(INDIRECT("1:"&ROWS(A1:A85)))))) The third column is the list that I use to fill in cells on a sheet called Bullets. www.contextures.com is where the formula came from, and the second formula is inputted as an array. However, when i go to the sheet called Bullets, the items do not come off of the list. What have I done wrong with the code or what am I doing wrong with the validated lists? -- static69 ------------------------------------------------------------------------ static69's Profile: http://www.excelforum.com/member.php...o&userid=11075 View this thread: http://www.excelforum.com/showthread...hreadid=345855 |
#3
|
|||
|
|||
static69 wrote:
I am using 3 columns of data. The first is my original list. the second contains this =IF(COUNTIF(Bullets!$A$1:$K$310,A1)=1,"",ROW()) the third contains this =IF(ROW(A1:A85)-ROW(A1)+1COUNT(B1:B85),"",INDEX(A:A,SMALL(B1:B85, ROW(INDIRECT("1:"&ROWS(A1:A85)))))) The reference to the Bullets sheet should be a column of the same size as the one referred to in the second equation (A1:A85). If you are following the format of Contextures, the first equation should read: =if(countif(Bullets!B1:B85,A1)=1,"",row()) |
#4
|
|||
|
|||
the actual sheet that uses the lists is variable in size....from a1:a133 to a1:337....all dependent on how many students i have each month. the formula was used in 4 different sheets that each contain 80+ bullet comments for testable areas. the point was to make it so that my instructors could not use the same bullet twice each class. if the formula cannot do it.....any ideas? -- static69 ------------------------------------------------------------------------ static69's Profile: http://www.excelforum.com/member.php...o&userid=11075 View this thread: http://www.excelforum.com/showthread...hreadid=345855 |
#5
|
|||
|
|||
The info at the www.contextures.com site is applicable and can be
adapted to your project. Maybe this slightly different, customized approach will help you: Open workbook with 2 sheets: Students, Bullets On sheet Students: Enter student names in a column named Student Create an equal sized column named Attribute EditGotoAttributeDataValidationSource: =Att_Check InsertNameDefineNames in workbook: Att_Check Refers to =OFFSET(INDEX(Remains1,1),0,0,COUNTA(Remains1)-COUNTBLANK(Remains1),1) On sheet Bullets: Enter bullet attributes in a column and name it Attr1. Create two columns of the size of Attr1 and name them Used1 and Remains1 Enter this formula into Used1: =IF(COUNTIF(Attribute,Attr1 R)=1,"", ROW(Attr1 R)-ROW(INDEX(Attr1,1))+1) Enter this array formula into Remains1: =IF((ROW(Attr1)-ROW(Attr1 R)+1)COUNT(Used1),"", INDEX(Attr1,SMALL(Used1,ROW(Attr1)-ROW(Attr1 R)+1))) Expand project by creating more bullet sheets and creating new names. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
making a non-validation list | Excel Worksheet Functions | |||
Need totals of values that fall within a given year | Excel Worksheet Functions | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions |