Turning vertical data into updating horizontal tables in new woorksheet

So if the thread title didn't give it away - I may have come up with a rather tricky problem for excel.

I have a load of data in a vertical column in one worksheet, which I wish to organise into 9x9 tables in a second worksheet, which updates as the first worksheet is edited. I've been searching the internet for a good hour trying to solve this problem, but can't come up with anything particularly useful.

I could copy the data 9 rows at a time from vertical to horizontal with the =TRANSCRIBE function, but I have a vast amount of data I want to do this with, so I'd rather find an easier way.

I think my request is vaguely similar to the following thread: http://www.excelforum.com/excel-gene...orizontal.html However the answer to that thread didn't help me very much (would maybe have been useful had the poster explained what various parts of the formula were doing so I could fiddle with it for my own uses).

So basically I want to copy one big vertical line of data into many smaller horizontal lines of data all in one go.

Any help would be very much appreciated, thanks.

hi,

Could upload a sample workbook, which would be easier and quicker way to arrive at a solution

How about, assuming your data is on Sheet1, starting in cell A1, and you want your new data to start on Sheet2, cell A1:

=INDIRECT("Sheet1!A"&((ROW()-1)*9+COLUMN()))

Obviously if your range of cells is different, this may need to be tweaked. But hopefully this will give you some idea?