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

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.

0
1/11/2005 12:52:42 AM
excel 39880 articles. 2 followers. Follow

4 Replies
533 Views

Similar Articles

[PageSpeed] 30

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

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
<petemccarthy@gmail.com> wrote in message
news:1105404762.544515.35210@f14g2000cwb.googlegroups.com...
> 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.
>


0
demechanik (4693)
1/11/2005 1:53:23 AM
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.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

<petemccarthy@gmail.com> wrote in message 
news:1105404762.544515.35210@f14g2000cwb.googlegroups.com...
> 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.
> 


0
nothanks4548 (968)
1/11/2005 3:12:21 AM
Can I do something similiar using Excel?

I've already created the form in Excel.

Earl Kiosterud wrote:
> 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.
>
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> <petemccarthy@gmail.com> wrote in message
> news:1105404762.544515.35210@f14g2000cwb.googlegroups.com...
> > 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.
> >

0
1/11/2005 5:54:41 PM
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.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Pete" <petemccarthy@gmail.com> wrote in message 
news:1105466081.559302.233850@z14g2000cwz.googlegroups.com...
> Can I do something similiar using Excel?
>
> I've already created the form in Excel.
>
> Earl Kiosterud wrote:
>> 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.
>>
>> --
>> Earl Kiosterud
>> mvpearl omitthisword at verizon period net
>> -------------------------------------------
>>
>> <petemccarthy@gmail.com> wrote in message
>> news:1105404762.544515.35210@f14g2000cwb.googlegroups.com...
>> > 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.
>> >
> 


0
nothanks4548 (968)
1/11/2005 8:25:34 PM
Reply:

Similar Artilces:

Emailing Worksheet from Excel
Hi all, I am trying to email a worksheet from excel 2003. I am following th directions, but it always attaches the entire workbook and not just th open worksheet. When troubleshooting, i notice I do not have the email icon on m toolbar, and help says this is because I do not have outlook on m computer. I do have Outlook 2003... is there a way I can associat these so I can send my worksheet? Many thanks in advance -- Message posted from http://www.ExcelForum.com Hi see: http://www.rondebruin.nl/sendmail.htm -- Regards Frank Kabel Frankfurt, Germany > Hi all, > > I am tryi...

data validation from other file.
i want to use data validation from other file. Like i have created a master.xls file & want to use data validation in other file where i can pickup product code from master.xls file. i don't want to use vlookup. please help me. Thanks in Advance Shital Shital Debra Dalgleish has instructions for this at.... http://www.contextures.on.ca/xlDataVal05.html Gord Dibben XL2002 On Wed, 5 Nov 2003 03:28:01 -0800, "shital" <shahshital@rediffmail.com> wrote: >i want to use data validation from other file. > >Like i have created a master.xls file & want t...

Delete cell and move to next
Hi all Can anyone help with a macro. It is supposed to check if the cell in column A is empty and if it is delete and move the contents of B into A on deletion. I would post my workings but am too ashamed to do so. Please help. Rob Rob, Not sure if you mean any cell in column A, and by move whether you want to shift everything over. You could use: Sub Macro1() On Error Resume Next Columns("A:A").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft End Sub which would do all cells at once, and shift everything over by one cell whereever there is a blank. If it is just a few c...

Copying certain column information quickly
As you know in Excel you could quickly copy the information you needed with a drag of the mouse. Because I need information copied repeatedly and easily for my access unfirmiliar co workers. Example JOB CUSTOMER QUOTE # A B1 110701 A(copied) A2 110701 (copied) A(copied) C4 110701 (copied) How do I do this quickly and efficiently for the computer challenged. Sorry, Access is a database, not a spreadsheet, and can't be used like one. You can write some code to select the rows that you want to ...

Macro to highlight every 2nd row
Hi, I am trying to work out how to write a macro so that it takes my data range and highlights every second row in a certain colour. if my data is in the range A2:K31, then i want the macro to run so it selects A3:K3 and highlights that in yellow, then skip down two rows and highlights A5:K5 in yellow and so on until there is no more data in the cells (ie. Row 31). the code I have so far is this, but it only highlights every second cell in column A and does NOT extend out to column K... Sub NEWY() Range("A2:K2").Select Do Until ActiveCell.Value = Empty ...

updating or changing data
Background: For a construction business, I use a Workbook Template fo each job. This template contains a separate worksheet for each Labor Equipment, Material, Other, and Subcontract Prices. Each of thes sheets has a different amount of "fields" to suit each individually. then have a sheet that I use for the estimate, and up to 60 worksheet for individual days to track or bill the job out. I have created ID' for each item in my pricing databases and use the VLOOKUP function t enter the appropriate items on the estimate or daily record sheets. The daily record sheets are set ...

How to delete repeted rows automaticaly?
Hello, I have a very big list of adresses and phones. There are a lot o repeted data in it. I would like to know if there is a way to erase the repeted rows of m list automaticaly. If it is possible, how can I do it? Thanks very much. Matheu -- gabarra ----------------------------------------------------------------------- gabarrao's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1688 View this thread: http://www.excelforum.com/showthread.php?threadid=32059 I would sort the rows (Data, Sort) and then delete the duplicated rows which will be obvious. Take a...

Outlook & Hotmail
Not sure how well this post will go down as there must be a lot of Outlook fans here - anyway here goes! I used *Outlook Express* with a *hotmail* account for as long as I can remember and had no problems Since the advent of the *outlook connector* combined with *Outlook 2007 and hotmail/windows live* I have been plagued with problems _All_I_want_to_do_is_check_mail_online_via_a_browser,_download_the_mail_to_a_local_client_and_be_able_to_keep_doing_that_however_large_my_mail_file_gets_ That simple task now seems impossible Yes Outlook with Exchange is fine - I come across v...

How to change the color of all series in an excel chart in one go. #2
In Excel 2000 I have a chart (XY) with a large number of series. Excel assigns different colours to each series. I would like to have the same colour for all series and then change a few to highlight them. Do I have to change them all one by one or can I have excel make a chart where the series all have the same colour (preferably chosen by me)? Marielle - Format one series the way you'd like them all to look. Select another series, and press the F4 key to repeat, Select another and press F4 again, and continue until you've changed them all. - Jon ------- Jon Peltier, Microso...

moving to exchange 2003 should we also do 2003 Server?
We are planning on moving from exchange 2000 to 2003. Are there any advantages to going with 2003 Server too or not worth the upgrade if the only reason is to upgrade exchange. Thanks for your comments. Exchange 2003 can not be installed on Windows 2000. This is due to some of the AD changes made in Win 2003 it requires. you can install Exchange 2000 on Windows 2003, but not Exchange 2003 on Windows 2000. So, if you want to use Exchange 2003 you have to have Windows 2003. "Dylan" <dbruntil@citysports.com> wrote in message news:2494b513.0408270735.9f0a4ff@posting.google.co...

Carry data forward or duplicate and erase
I am creating a very simple time entry form with an Add Record button. I’d like the employee to enter their name in the name field one time and then every time they press the Add Record button their name would be carried forward to the next record. Or, alternatively, duplicate the record and clear other fields. I’ve done a lot of work with forms and reports, but not much programming experience, so the simpler, the better. Thank you in advance for any help. Try this article, written by Access MVP Allen Browne: Assign default values from the last record http://a...

Quick help with excel formula
Ok, i need a forumla. this is what I want, i have two columns like this Time Start: ............. Time Finish: ............ I fill out these two columns like this Time Start: ...17:42... Time Finish: ...18:25.. Now i have another Column that says Training Length: ........ I want this to work out the time between 17:42 and 18:25 Any one help pleas -- Message posted from http://www.ExcelForum.com Hi simply =A2-A1 and format as time If your training time can span midnight use the formula =A2-A1+(A2<A1) >-----Original Message----- >Ok, i need a forumla. > >this is what ...

where these data come from?
SmartList -> Account Summary, these Credit Amount and Debit Amount data come from which table? which fields? Please advise. Thank you very much. stien Stien, It is GL00100 (Account Summary) and GL10110 (Account Current Summary Master). Hope it helps. Girish "stien" wrote: > SmartList -> Account Summary, these Credit Amount and Debit Amount data come > from which table? which fields? > > Please advise. Thank you very much. > > stien Thank you very much. stien "Girish Viriyur" wrote: > Stien, It is GL00100 (Account Summary) and GL10110...

Moving Guide Mover4u.com
Moving Guide Mover4u.com - World Moving & Storage Local movers, Long Distance Moving Relocation to new State, Cross Country Movers. packers and movers. Whether this is your first or fifth move, moving to a new home or even a new town can be an exciting experience. However, relocating also has the potential to be quite stressful. By planning ahead and considering all the specifics involved, your move can be a smooth one. free moving estimate http://www.mover4u.com call us 818-439-3474 At Mover4u.com we are determined to provide you with all the necessary resources to assist you with your...

Moving CRM 3.0 from one server & domain to another server & domain
We need to move CRM 3.0 from one domain (say Prod1) to another domain (say test1). We need to create a "copy" of our current production env. so that we can continue the development and testing elsewhere. The versions of software on both env. are same. I have tried to read / locate as much helpful info on this as possible. There are workflows and custom entities. Would one use the Export / Import Customizations followed by data migration tool (I believe this does not allow for the custom entities ?). Database backup and restore on another server --- will this work ? What are t...

How to auto-fill text based on text in another cell
Hello, I need to know how to auto-fill text based on text in another cell. For example: Every time I enter "CHS" in Column B, I want Charleston to auto-fill in Column C. And when I enter "SAN", in Column B, I want San Diego to auto-fill in Column C. How can I set up a list like this? Any ideas? Thanks! Jason One way is to create a table of airport codes and cities, then use VLOOKUP. See here for instructions: http://www.contextures.com/xlFunctions02.html HTH Jason Atlanta, GA >-----Original Message----- >Hello, > >I need to know how to auto-fill ...

sum weekly data into monthly
I want to sum weekly data into monthly data by SKU. I have two tables. the first looks like this: sku 20100426 20100503 20100510 20100517 20100524 abc 200 300 200 150 150 where the column heading of 20100426 = week ending 4/26/2010 the second table looks like this: DATE Fiscal Month 20100426 6 20100503 6 20100510 6 20100517 7 20100524 7 I want to sum SKU abc by fiscal mont. For month 6 it would equal 700 month 7 would equal 300. hate to admit it but this one is beyond me. ?!? You have a column named for the "week ending date?&...

can u have a name that is a ref to cells in a worksheet in an add-
I would like to have a cell that I have added Validation to and would like to specify list and then use a =name as the source. I would then like the name to be a reference to some cells on a worksheet in an add-in, is this possible? I have created the name in the xla, but I can not simply use =name as the source in my other workbook, it complains about not be able to find the name. -- Trefor Debra Dalgleish explains it all: http://contextures.com/xlDataVal05.html Trefor wrote: > > I would like to have a cell that I have added Validation to and would like to > specify list and...

One Email
This is a multi-part message in MIME format. ------=_NextPart_000_0037_01C4A170.68CA1620 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there some way to have one email accessed by many Outlook 2003 = clients? =20 My situation is I have a main email such as work@myemail.com where all = work orders are sent to. I have a couple data entry people servicing = these individual emails. So what would work great is if both Outlooks' = were somehow pointed to the same PST file. So if one of the girls = deletes the email on her compute...

Move mailboxes #7
Hi all, Can anyone give some advice on moving mailboxes from exchange 2000 to exchange 2003 server without any disruption if I schedule to do a move at night time? How do we prevent users not to access their mailboxes? thanks. Logons should be automatically disconnected and logons limited during a mailbox move. Nue "Jane" <Jane@discussions.microsoft.com> wrote in message news:9143CE80-3EAB-4FA7-80B9-DEB35BFCDDE0@microsoft.com... > Hi all, > > Can anyone give some advice on moving mailboxes from exchange 2000 to > exchange 2003 server without any disruption ...

moving first word in a column to a separate column
In an Excel 2007 with 26,000 rows I have a column that contains names with 2 or 3 words. Example: John Andrew McPherson Phillip something something Anderson I would like to split this into 2 columns, one for the first name and the other for the last word (which may be the second or 3rd or even 4th). What would be a way to automatically move: John to a Given Name column and McPherson to the surname column, and Phillip to the Given Name column and Anderson to the surname column. Thanks. Jeff See Chip's site for extracting first, middle and last names. http://www.cpearson.com/excel/F...

Help! How can I upload data and display it in Excel on client
side. Also, how can I open a book with several sheets on client side? (It is a web application). Thanks. ...

Quick Question
I have been using Office 2007 and OneNote for some time now and have recently set up the 2010 Beta on a second system. In the 2007 version there was a way to repair office if things went flaqky. How do I do that in Office 2010? Michael On Tue, 8 Dec 2009 23:59:26 +0000 (UTC), Rev. Michael L. Burns <sdbpastor@charter.bet> wrote: > >I have been using Office 2007 and OneNote for some time now and have recently >set up the 2010 Beta on a second system. In the 2007 version there was a >way to repair office if things went flaqky. How do I do that in Office 2...

How to select data series to format? (alternative needed)
Is there an alternate way to select a data series to format in an XY Scatter chart? The only way I know of is to move the cursor close to the series line and right-click. But if the chart includes many data series and the lines are close together, it is difficult, if not "impossible", to select the desired line. I am looking for a method that presents me with a list of all the data series, and I select the one I want to "right click" (i.e. open the menu that include format, add trendline and clear, among others). Or something else that would not be so difficult to use. ...

Pivot table format on paste values
Hi, I've constructed a working file with numerous pivot tables to be used in reports. what i would like to do is paste these sheets into a new book as values and still retain the Excel 2007 formats. As i have multiple pivot tables on one worksheet when i copy and paste into the new sheet i lose all the pivot table formatting eg total lines and the standard blue headings? it seems i can simply highlight one pivot and copy and paste this as values and source formats but it does not work when copying the whole sheet - which i need as this is a very repetitive task if i have to r...