Another play which should deliver it ..

Assuming source data in Sheet1 is within cols A to G as posted, from row1 down

In Sheet2,

In A2:
=OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/5),)

In B2:
=OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/5),)

In C2:
=OFFSET(Sheet1!$C$1,,MOD(ROWS($1:1)-1,5))

In D2:
=OFFSET(Sheet1!$C$2,INT((ROWS($1:1)-1)/5),MOD(ROWS($1:1)-1,5))

Select A2:D2, copy down as far as required.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tina" wrote:
I have a spreadsheet which contains a series of data that spreads accross
many columns and four fows. This data repeats itself over and over every four
rows down. In the example below, each course is offered in four rooms, the
following columns represent days 1-5 and the hours logged in each room for
the specific class.

Sheet1 contains:
Course Room 1 2 3 4 5
Transition Red 8 8 8 8 8
Transition Blue 2 2 2 5 5
Transition Green 0 0 0 0 0
Transition Yellow 1 2 2 2 2
STAR Red 8 8 8 8 2
STAR Blue 2 2 1 1 1
STAR Green 4 4 4 4 4
STAR Yellow 8 8 8 8 8

What I need to do, is keep this spreadsheet view for easy to data enter
purposes but link it to another spreadsheet that converts it into standard
database format. See example below:

Sheet2 contains:
Course Room Day Hrs
Transition Red 1 8
Transition Red 2 8
Transition Red 3 8
Transition Red 4 8
Transition Red 5 8
Transition Blue 1 2
Transition Blue 2 2
Transition Blue 3 2
Transition Blue 4 5
Transition Blue 5 5
etc ...

Is there a simple formula I can enter into each column that I can easily
drag/copy down the rows to continue to select the repeating series of 4 rows
and 7 columns? I'd like to have the formula so that I can continue to drag it
down to link to any new data entered at the end of the table that the data is
entered in.

Can you help me?