Offset formula for copying rows to columns

[CLoos]

Hi - I have been struggling with this for months. I put it away and then come back to it. There was a request for a simple user spreadsheet to create a interactive graph dashboard. So the idea is to take the spreadsheet "Special Projects" and have the week numbers as a basis of change. (starting from column O, row 1). Then translate this to formula's on sheet1 so as you change the "Special Projects" week number values, sheet1 also changes.

Everything past that I have developed nicely. The pivots charts etc.

In columns C - L on Sheet1 I want to be able to drag the formula down for each row associated to the Special Projects worksheet. You can see I have manually pasted the rows in a static position now for each week #. I tried several ways unsuccessfully. I saw another post about using an offset formula and I tried that but could not make it work.

Is there a formula that I could drag down a specified about of times, depending on how many week #'s I have and then repeat for the same amount of blocks of weeks over and over again?

 

[Khalid NGO]

Hi,

Try this in Sheet1, C2:

=INDEX('Special Projects'!$B$2:$M$9,INT((ROWS($A$1:A1)-1)/COLUMNS($O$1:$AL$1))+1,MATCH(C$1,'Special Projects'!$B$1:$M$1,0))

Copy across and down...

Regards,

 

[CLoos]

This is great! I will have to tear it apart so I can understand it. I am having an issue with copying down?? It keeps repeating row 2.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[CLoos]

Thank you

 

[CLoos]

Hi ,

See if this is OK.

Narayan

Thank you!