School Spreadsheet

[asherlevin]

Here is Sheet 1.

This is a student grade book. If a student has an x under a topic, that means they have achieved that goal.
Test has therefore achieved 'Recognise the use of binary numbers...' etc.

Here is sheet 2.

When a student types their name: 'Test' in this example -> I want the next cell goal which doesn't have an X next to it, to appear on this sheet where it says 'Next Goal'.

So the next goal, on sheet 1' is: use binary in computer registers...etc.. When I type in X on sheet 1, it will then jump to the next goal for Test.

 

[FDibbins]

Re: Help Needed for a School Spreadsheet

You have a sheet with all students names
That list contains columns with all courses they can take
Starting from left to right, they complete classes sequentially
As they complete a course, it gets x'd off
On another sheet, you want the student to be able to type in their name, and it shows what their next course is

If that is what you want, then my 1st suggestion will do that for you. You just need to empty Column A on my Extract sheet, only use 1 row for the formula, and that is where the student will enter their name - it will then show them what their next course is

 

[asherlevin]

Re: Help Needed for a School Spreadsheet

You have a sheet with all students names
That list contains columns with all courses they can take
Starting from left to right, they complete classes sequentially
As they complete a course, it gets x'd off
On another sheet, you want the student to be able to type in their name, and it shows what their next course is

If that is what you want, then my 1st suggestion will do that for you. You just need to empty Column A on my Extract sheet, only use 1 row for the formula, and that is where the student will enter their name - it will then show them what their next course is

Many thanks. I am using Google Sheets. Unfortunately by preempting the index with a column reference (B), it turns it from a formula into simply text.

 

[asherlevin]

Re: Help Needed for a School Spreadsheet

Many thanks. I am using Google Sheets. Unfortunately by preempting the index with a column reference (B), it turns it from a formula into simply text.

So close! How do I get it to do it for the specific student name though. Any ideas?

 

[FDibbins]

Re: Help Needed for a School Spreadsheet

OK sorry, I cant help you with that, I don't use Sheets, but I know there it has an equivalent for INDEX

 

[FDibbins]

Re: Help Needed for a School Spreadsheet

So close! How do I get it to do it for the specific student name though. Any ideas?

You could add another MATCH to find the student name, but then the COUNTIF wont work. Let me take another look for you

 

[FDibbins]

Re: Help Needed for a School Spreadsheet

OK this works in Excl, you will just have to try and see if it works in Sheets.

Using the same data sample...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]data sheet[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Name[/td][td]xx[/td][td]zz[/td][td]Class1[/td][td]Class2[/td][td]Class3[/td][td]Class4[/td][td]Class5[/td][/tr]

[tr][td]

3​

[/td][td]aa[/td][td][/td][td][/td][td]x[/td][td]x[/td][td]x[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]bb[/td][td][/td][td][/td][td]x[/td][td]x[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]cc[/td][td][/td][td][/td][td]x[/td][td]x[/td][td]x[/td][td]x[/td][td][/td][/tr]
[/table]

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]extract sheet[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td]Class4[/td][/tr]

[tr][td]

3​

[/td][td]bb[/td][td]Class3[/td][/tr]

[tr][td]

4​

[/td][td]cc[/td][td]Class5[/td][/tr]
[/table]
B2=INDEX(Sheet2!$D$2:$H$2,COUNTA(INDEX(Sheet2!$D$3:$H$5,MATCH(A2,Sheet2!$A$3:$A$5,0),0))+1)
Or
=I(A2="","",INDEX(Sheet2!$D$2:$H$2,COUNTA(INDEX(Sheet2!$D$3:$H$5,MATCH(A2,Sheet2!$A$3:$A$5,0),0))+1))

 

[asherlevin]

Re: Help Needed for a School Spreadsheet

Struggling to this. Please can you help? Here is the link. So sorry!

https://docs.google.com/spreadsheets/d/1ya-6iG1olAtCG2KSmuRxpIPxhaZZsKMKjiLNW0Lcv9Y/edit?usp=sharing

 

[FDibbins]

Re: Help Needed for a School Spreadsheet

As I mentioned, I don't use Sheets (and cannot even access it from here (work), Perhaps find a forum that is specific to Sheets and see if they can "translate" my suggestion? Im not even sure if that technique will carry over