Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automating transposition exercise
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automating transposition exercise
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) -- Thanks, Shane Devenshire "Bob Matthews" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automating transposition exercise
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 "ShaneDevenshire" wrote in message ... 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) -- Thanks, Shane Devenshire "Bob Matthews" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
training exercise | New Users to Excel | |||
Transposition impossibility? | Excel Discussion (Misc queries) | |||
Lottery exercise | Excel Worksheet Functions | |||
A 13 period exercise | Excel Worksheet Functions | |||
Transposition | Excel Worksheet Functions |