Dragging a formula problem

[Losguapos1]

There may be an easy solution to this problem but i cannot seem to get it.

I have this formula in a cell ='Teacher Summary'!C4. i want to drag it down and fill it with ='Teacher Summary'!D4, then ='Teacher Summary'!E4 , etc.

But it keeps going to ='Teacher Summary'!C5, then ='Teacher Summary'!C6 etc.

Any help would be greatly appreciated

George

[jeffreybrown]

Maybe one way...

In G1 put Teacher Summary and then...

Please Login or Register  to view this content.

>> Drag down

[Losguapos1]

so that worked for some of the cells the first 24 but the next 22 were not right? any idea as to why that might be?

[jeffreybrown]

No. I can make stuff work on this test spreadsheet I have here, but in the end, I don't know what you are working on. Can you post a test workbook?

Edit: What is the reference for the 25th? Is it 'Teacher Summary'!AA4?

Where are you pasting this formula? What row does it start on?

[Losguapos1]

So i attached a sample file, hopefull this helps. I need to get all of the data onto the other sheet.

[jeffreybrown]

See how this works...

In C3 drag down and to the right...

=INDIRECT("'"&$AB$3&"'!"&SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")&C$1+IF(C$1<13,3,4))

[Losguapos1]

thanks that is awesome !!!!!

[jeffreybrown]

You're welcome…glad you have a workable solution and thanks for the feedback...

[benishiryo]

hi Losguapos1. seems like your data is very nicely sorted, so here's an alternative:
=INDEX('Teacher Summary'!$C$4:$AV$24,COLUMNS($C3:C3),ROWS(C$3:C3))

or if they are not sorted,
=INDEX('Teacher Summary'!$C$4:$AV$24,MATCH(C$2,'Teacher Summary'!$B$4:$B$24,0),MATCH($B3,'Teacher Summary'!$C$3:$AV$3,0))

[jeffreybrown]

Hi benishiryo,

This does not appear to work as there is a gap in the data between the Merits and the Demerits...

=INDEX('Teacher Summary'!$C$4:$AV$24,COLUMNS($C3:C3),ROWS(C$3:C3))

The Index/Match seems to be a great alternative. I had my mind to tunnel focused on the indirect...Thanks.

[benishiryo]

@jeffrey:
yeah you're right about that. i guess you can slot in the formula i did in another one:
=INDEX('Teacher Summary'!$C$4:$AV$24,MATCH(C$2,'Teacher Summary'!$B$4:$B$24,0),ROWS(C$3:C3))

=)
i usually try to avoid INDIRECT & OFFSET if i can as they are volatile