Copy 1 Row to Multiple Rows

F

FoMoCo-Tom

I have date in one worksheet (A) in rows. I need to reposition the data into another worksheet (B) such that it uses several rows. After entering the formulas once, I want to copy and paste the first set of formula rows in B back into B for each row in A

The problem? Lets say I have 1 row of data (A) going into 3 rows of data (B). When I copy the formulas (B), the first row of the new set (row 4)(B) will reference the 4th row of the source data (A) when I want it to reference the second row of the source data (A). The next copy will have the first row (row 7) (B) referencing the 7th row of the source (A) when I want it to reference the 3rd row of the source (A)

How do I get around it? One though was moving the data into MS Access and process a form. Fine for me, but what if I want to share the application to ..... the "less fortunate" who are not power users

My apologies for the description, I am an Engineer.
 
M

Max

If I've read your intent correctly ...

You have
in sheet: "A"
---------------
1 2 3
4 5 6
7 8 9
etc

In sheet: "B"
----------------

Put in say*, A1:

=OFFSET(A!$A$1,INT((ROW(1:1)-1)/3),MOD(ROW(1:1)-1,3))

Copy A1 down as many rows as there is data items in sheet: "A"

(or until zeros appear, which signals the exhaustion of data,
provided there are no zeros within the data)

Col A will return:

1
2
3
4
5
6
7
8
9
etc

*The formula can be placed in any cell in row1 (e.g.: in B1, C1, etc), and
copied down
---
If, instead of 3 columns, you have 4 columns of data in "A" to be translated
in "B"
just change the "3" to "4" inside the formula. And so on ..

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
FoMoCo-Tom said:
I have date in one worksheet (A) in rows. I need to reposition the data
Click to expand...
into another worksheet (B) such that it uses several rows. After entering
the formulas once, I want to copy and paste the first set of formula rows in
B back into B for each row in A.
The problem? Lets say I have 1 row of data (A) going into 3 rows of data
Click to expand...
(B). When I copy the formulas (B), the first row of the new set (row 4)(B)
will reference the 4th row of the source data (A) when I want it to
reference the second row of the source data (A). The next copy will have the
first row (row 7) (B) referencing the 7th row of the source (A) when I want
it to reference the 3rd row of the source (A).
How do I get around it? One though was moving the data into MS Access and
Click to expand...
process a form. Fine for me, but what if I want to share the application to
...... the "less fortunate" who are not power users?
 
F

FoMoCo-Tom

Its more lik

Sheet A
Row1: 1 2 3 4 5
Row2: 7 8 9 10 11 1

Where the Rows are line items (e.i. Row1 Data for Fred and Row2 Data for Ellen

Sheet B
Row1: 2
Row2: 1
Row3: 3
Row4: 8 1
Row5: 7 10
Row6: 9 1

I though of alternative ways by importing the data from EXCEL into ACCESS or even mail merge with WORD. I fine with jumping around applications. But if I pass my method off to others, they are not comfortable. I wanted to keep in all in one package.
 
M

Max

Maybe give this a try ..

Assuming the data
(from your post)
Sheet A:
Row1: 1 2 3 4 5 6
Row2: 7 8 9 10 11 12
Click to expand...

is in A1:F2

In Sheet: "B"
--------------
Enter into C1:D3

B1 E1
A1 D1
C1 F1

The above are the target cell addresses in sheet: "A"'s row1 for the 1st set

Select C1:D3
Copy down to D6

This will produce in C4:D6

B2 E2
A2 D2
C2 F2

which nicely turns out to be
the target cell addresses in sheet: "A"'s row2 for the 2nd set

Now just put in A1: =INDIRECT("A!"&C1)

Copy A1 across to B1, then copy down to B6

In A1:B6 will be returned the desired results
as indicated in your post, viz.:
Sheet B:
Row1: 2 5
Row2: 1 4
Row3: 3 6
Row4: 8 11
Row5: 7 10
Row6: 9 12
Click to expand...

----
Extend to suit

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
FoMoCo-Tom said:
Its more like

Sheet A:
Row1: 1 2 3 4 5 6
Row2: 7 8 9 10 11 12

Where the Rows are line items (e.i. Row1 Data for Fred and Row2 Data for Ellen)

Sheet B:
Row1: 2 5
Row2: 1 4
Row3: 3 6
Row4: 8 11
Row5: 7 10
Row6: 9 12

I though of alternative ways by importing the data from EXCEL into ACCESS
Click to expand...
or even mail merge with WORD. I fine with jumping around applications. But
if I pass my method off to others, they are not comfortable. I wanted to
keep in all in one package.
 
Top