Quickly moving down one row on pasted data from another worksheet.

  • Thread starter Thread starter petemccarthy
  • Start date Start date
P

petemccarthy

Ok, this should be fairly simple but I'm a excel novice. I have two
worksheets. One contains all data with each row representing a
different store. I then have another worksheet that is a form. I
need to fill out a form for each store. The information maps the same
every time, just one row down. It looks like this...

WORKSHEET1
A B C D E

1 A1 B1 C1 D1 E1

2 A2 B2 C2 D2 E2



WORKSHEET2

Name: =Worksheet1!A1

Company: Worksheet1!B1

Parts: Worksheet1!C1

----------------------------------------------------------
How do I have it so I can be on Worksheet2 and automatically have the
data move down one row and then print off the form. I have 500 of
these to do with lots of info.

I tried this:

WORKSHEET2

Name: =Worksheet1!A(1 + Worksheet2!F6) and then changing the value
of F6 by 1 each time....but obviously that didn't work.
What's the trick.

Thanks in advance.
 
One guess as to what you're after ..

Assume you have

In Sheet1
------------
in cols A to C, data in row1 down

Name1 Comp1 Part1
Name2 Comp2 Part2
Name3 Comp3 Part3
etc

In Sheet2
------------
Put the "labels" below in A1:A3

Name:
Comp:
Part:

Put in B1:

=OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/3),MOD(ROWS($A$1:A1)-1,3))

Copy B1 down to B3

Select A1:B3

Fill down as many rows as needed
to exhaust the data in Sheet1
(zeros in col B will signal data exhausted),
terminating the fill-down at a multiple of 3 rows,
e.g.: at B6, or B9 or B12 etc

For the sample data,
you'll get in cols A and B:

Name: Name1
Comp: Comp1
Part: Part1
Name: Name2
Comp: Comp2
Part: Part2
Name: Name3
Comp: Comp3
Part: Part3
etc

Adapt to suit

If you have 4 cols in Sheet1
to transpose vertically in col B in Sheet2
(instead of the sample 3 cols),
just change the "3"'s in the formula in B1 to "4"'s, viz.:

change:
.... INT((ROWS($A$1:A1)-1)/3),MOD(ROWS($A$1:A1)-1,3))

to:
.... INT((ROWS($A$1:A1)-1)/4),MOD(ROWS($A$1:A1)-1,4))

and then copy B1 down correspondingly to B4, etc
 
Pete,

Do you mean that each form should have the data from a row, and the next
form should have data from the next row, and so on? This sounds like a good
job for a mail-merge with Microsoft Word. You can use your Excel table. If
your intent is to print them, this is probably what you need.
 
Pete,

You could, but you'd have to write a macro. Excel prints the sheet as-is,
giving you no options apart from what's in Page Setup. There's no "one row
per form" as with an Access report (printout) or a Word mail-merge. You can
have your list in Excel, and do the mail merge in Word, using the Excel
list. You can also use Access, and still use the Excel list.
 
Back
Top