#### 2k05gt

##### Board Regular

- Joined
- Sep 1, 2006

- Messages
- 151

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: