Automatically transpose information to another sheet

[Mark Horsfall]

Hello

I was wanting to put the names from the first sheet into the second sheet to show progress of children.

So all those children who in Autumn term who were NYR go in row 10 but in the assessment stage they achieved in spring term.

So for example Umar the first child I want to go in E10 on Sheet 1

I don't know what formula I could use to automatically do this and how I could do it for all the children in the reading stage progress (the first sheet).

Can you help. I prefer to use formulas than macros as I have a better understanding of those. On the first sheet I used an index formula I am not sure if I would need to use something similar for what I desire here.

I have attached the file

Hope this is possible and I hope you can help

Mark

 

[Luke M]

There's not enough space to do what you want. For instance, in the Spring, there were 17 kids in Progressing Category. However, on Sheet1, there are only 8 cells under the Progressing category that line up with the labels in A4:A11. Thus, we have a problem of trying to put 17 items into 8 slots. How do you want to overcome this problem?

 

[Mark Horsfall]

I am not sure.

I was hoping for ideas..... could I change the layout to do something similar would that work?

I want to see progress from Autumn to Spring, Spring to Summer etc

 

[Luke M]

Are the categories in a specific order, ie showing a progression in the order they are currently in?

I'm thinking a better model would be to have student names on left, time period as column headers, and fill in the intersection with what they were in.

If categories indicate progression, perhaps add some conditional formatting to give impression of time progress? Light green for beginner program, and dark green for best program?

 

[Mark Horsfall]

The stages are an progression.

How would the cells update automatically

What formula would I use?

 

[Mark Horsfall]

There's not enough space to do what you want. For instance, in the Spring, there were 17 kids in Progressing Category. However, on Sheet1, there are only 8 cells under the Progressing category that line up with the labels in A4:A11. Thus, we have a problem of trying to put 17 items into 8 slots. How do you want to overcome this problem?

Hello again (I had a thought last night)

I have changed the format of Sheet 2 to include extra cells like you requested. There are 24 cells for each stage.

I have attached the file

Would you be able to help now?

Mark

 

[NARAYANK991]

Hi Mark ,

This is just as an option ; Luke can probably give you what you are looking for.

Narayan

 

[Mark Horsfall]

Hi Mark ,

This is just as an option ; Luke can probably give you what you are looking for.

Narayan

Hello

Can you explain how that works - I do not understand.

Somebody sent me this formula but once again not sure how it works

=IFERROR(INDEX('Reading Progress'!$A$4:$A$69,SMALL(IF(($A$40='Reading Progress'!$B$4:$B$69)*($K$3='Reading Progress'!$C$4:$C$69),ROW($A$4:$A$69)-ROW($A$4)+1,""),ROWS($A$4:A4))),"")

This seems to fit my needs but not sure how I can copy it to the other cells.

If you could explain either to me that would be great.

I have attached the document again.

Mark

 

[NARAYANK991]

Hi Mark ,

Sorry , but your proposed format is not so amenable to simple formulae ; if you can stay with what I have showed , that is your Summary sheet ; selecting from the DV dropdown in C1 automatically populates the column E with the names of those students who are in that stage in the first (Autumn 1) term , and the other columns with their progress over the remaining terms.

The formulae are already in place ; so is the Conditional Formatting.

Replicating all of this using your proposed format is not my cup of tea ; possibly Luke will help.

Narayan

 

[Mark Horsfall]

Hi Mark ,

Sorry , but your proposed format is not so amenable to simple formulae ; if you can stay with what I have showed , that is your Summary sheet ; selecting from the DV dropdown in C1 automatically populates the column E with the names of those students who are in that stage in the first (Autumn 1) term , and the other columns with their progress over the remaining terms.

The formulae are already in place ; so is the Conditional Formatting.

Replicating all of this using your proposed format is not my cup of tea ; possibly Luke will help.

Narayan

Ok thanks - but still not understanding how what you have shown me works to replicate it- but thank you for your time and idea I will study it

I may need to think about how I can change the format of the sheet to help show progress from term to term.

 

[Mark Horsfall]

Hey

Just to let you know the formula that worked for me was

=IFERROR(INDEX(Progress!$A$4:$A$69,SMALL(IF(($A$16=Progress!$B$4:$B$69)*($C$3=Progress!$C$4:$C$69),ROW($A$4:$A$69)-ROW($A$4)+1,""),ROWS($A$4:A31))),"")

Just in case anyone has a similar query

I can attach the file if required