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

A formula cannot move data. You need a macro to move data

--
Don Guillett
SalesAid Software

"static69" wrote in message
...

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(INDIRE
CT("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

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())

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

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.