making used values fall from a list

S

static69

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)+1>COUNT(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?
 
H

Herbert Seidenberg

static69 said:
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
Click to expand...
=IF(ROW(A1:A85)-ROW(A1)+1>COUNT(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())
 
S

static69

the actual sheet that uses the lists is variable in size....from a1:a13
to a1:337....all dependent on how many students i have each month. th
formula was used in 4 different sheets that each contain 80+ bulle
comments for testable areas. the point was to make it so that m
instructors could not use the same bullet twice each class. if th
formula cannot do it.....any ideas
 
H

Herbert Seidenberg

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
Edit>Goto>Attribute>Data>Validation>Source: =Att_Check
Insert>Name>Define>Names 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.
 
Top