2k05gt
Board Regular
- Joined
- Sep 1, 2006
- Messages
- 157
I am having trouble with A FORMULA
Sheet layout.
A Student.....B Task.............C Completed
1 Student#1..........................## Completed =COUNTA(INDIRECT(C2&:&ADDRESS(MATCH(TRUE,INDEX((A3:A200<>0),0),0),3),FALSE),"Y")
2..................Homework..........y
3..................Match.............. y
4..................Reading.............y
5..................Writing..............y
6..................Geography.........n
7..................Book Report.......n
8..................Science Project...y
9
10 Student#2..........................## Completed =COUNTA(INDIRECT(C11&:&ADDRESS(MATCH(TRUE,INDEX((C11:C200<>0),0),0),3),FALSE),"Y")
11................Homework..........y
12................ Match...............y
13................Reading.............y
14................Writing..............y
15................Geography.........n
16................Book Report.......n
17................Science Project...n
18................Fitness Award.....y
19................Baseball Team....y
20................Soccer Team......y
21
22 Student#3..........................## Completed =COUNTA(INDIRECT(C23&:&ADDRESS(MATCH(TRUE,INDEX((C22:C200<>0),0),0),3),FALSE),"Y")
23................Homework.........y
24................Match................y
25................Reading.............y
26................Writing..............y
27................Geography.........n
28................Science Project...y
29
30 Student#4..........................## Completed =COUNTA(INDIRECT(C31&:&ADDRESS(MATCH(TRUE,INDEX((C31:C200<>0),0),0),3),FALSE),"Y")
Formula in C1, C10, C22, C30 (Does not work but its close to what I need "I think")
I need to get the Cell Address for the blank rows 9, 21, 29 and so on
I think I need it stored in a cell for the next student row to use..
Since the rows will always fluxuate I can not rely on the last row being equal betewwn each student,
since their goals are different.
the Rows with the student name and goals are Grouped and I can click the "+" to colapse the students rows
so just the students name row is only visable
Sheet layout.
A Student.....B Task.............C Completed
1 Student#1..........................## Completed =COUNTA(INDIRECT(C2&:&ADDRESS(MATCH(TRUE,INDEX((A3:A200<>0),0),0),3),FALSE),"Y")
2..................Homework..........y
3..................Match.............. y
4..................Reading.............y
5..................Writing..............y
6..................Geography.........n
7..................Book Report.......n
8..................Science Project...y
9
10 Student#2..........................## Completed =COUNTA(INDIRECT(C11&:&ADDRESS(MATCH(TRUE,INDEX((C11:C200<>0),0),0),3),FALSE),"Y")
11................Homework..........y
12................ Match...............y
13................Reading.............y
14................Writing..............y
15................Geography.........n
16................Book Report.......n
17................Science Project...n
18................Fitness Award.....y
19................Baseball Team....y
20................Soccer Team......y
21
22 Student#3..........................## Completed =COUNTA(INDIRECT(C23&:&ADDRESS(MATCH(TRUE,INDEX((C22:C200<>0),0),0),3),FALSE),"Y")
23................Homework.........y
24................Match................y
25................Reading.............y
26................Writing..............y
27................Geography.........n
28................Science Project...y
29
30 Student#4..........................## Completed =COUNTA(INDIRECT(C31&:&ADDRESS(MATCH(TRUE,INDEX((C31:C200<>0),0),0),3),FALSE),"Y")
Formula in C1, C10, C22, C30 (Does not work but its close to what I need "I think")
I need to get the Cell Address for the blank rows 9, 21, 29 and so on
I think I need it stored in a cell for the next student row to use..
Since the rows will always fluxuate I can not rely on the last row being equal betewwn each student,
since their goals are different.
the Rows with the student name and goals are Grouped and I can click the "+" to colapse the students rows
so just the students name row is only visable
Last edited: