Automating transposition exercise

[Bob Matthews]

Hi there

I have a workbook with many worksheets - all set out in an identical manner.

I wish to combine the data in all the identical worksheets into one new
worksheet where the data from each original worksheet takes up one row only
in the new worksheet.

I am using the OFFSET function, an example is as follows:-

The new worksheet has headings in Row 1
The formulae in Row 2 are as follows...........

Cell A2: =OFFSET(Sheet1!$B$1,(ROW()-2)*4,0)
Cell B2: =OFFSET(Sheet1!$B$1,(ROW()-2)*4+1,0)
Cell C2: =OFFSET(Sheet1!$B$1,(ROW()-2)*4+2,0)

For Row 3 I would be requiring the following:-

Cell A3: =OFFSET(Sheet2!$B$1,(ROW()-3)*4,0)
Cell B3: =OFFSET(Sheet2!$B$1,(ROW()-3)*4+1,0)
Cell C3: =OFFSET(Sheet2!$B$1,(ROW()-3)*4+2,0)

Is there any way to automate copying these formulae downwards in the new
worksheet where the Sheet Name and offset numbers are updated?

Bob M

 

[ShaneDevenshire]

Hi

Here is an example of what row 3 or 2 might look like

=OFFSET(INDIRECT("Sheet"&ROW(A1)&"!$B$1"),(ROW()-2-ROW(A1))*(3+ROW(A1)),0)

 

[Bob Matthews]

Hi Shane

would you care to explain you previous suggestion a little please........

My main problem is how to automate the formulae so that they move from one
worksheet to the next - the worksheet names are names like A114_N, A257_N,
McDonald(1), SmithJ(2) etc. etc. all different