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

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.
 
M

Max

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
 
E

Earl Kiosterud

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.
 
E

Earl Kiosterud

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top