copy selected rows to second worksheet (NOT Cut + Paste)

I am trying to create a simple tool log that also incorporates a sign in/out 
sheet as a second worksheet.
What I want is to be able to select (not using cut + paste) several rows and 
by simply being selected on "tool list" worksheet, temporarly copied into 
"sign_in" and "sign_out" worksheets.
I need the data selected from sheet 1 "tools list" to fill rows (starting at 
20) of the next 2 sheets, and then end user simply prints needed sheet, for 
employee to sign.

This allows me to select only the tools that that employee is checking 
in/out at that time and print that list from a second (or third) sheet along 
with data in previous 20 lines.

I would prefer to be able to keep this workbook protected, but it is not a 
nessessity.

0
Utf
6/3/2010 11:01:45 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
1326 Views

Similar Articles

[PageSpeed] 15

Let's say you mark the rows you want by placing an "X" in column A. To pull 
column B of selected rows, could do this array* formula:

=IF(ROWS(A$20:A20)>COUNTIF('Sheet 1'$A:$A,"X"),"",INDEX('Sheet 
1'!B:B,SMALL(IF('Sheet 1'!$A$1:$A$2000="X",ROW($A$1:$A$2000)),ROW($A1))))

Copy this over to the right as needed, and then down far enough to allow the 
maximum amount of entries. If you are splitting this over multiple sheets, 
and a set amount (say 100) entries go on first sheet, modify as so:

=IF(ROWS(A$20:A20)+100>COUNTIF('Sheet 1'$A:$A,"X"),"",INDEX('Sheet 
1'!B:B,SMALL(IF('Sheet 
1'!$A$1:$A$2000="X",ROW($A$1:$A$2000)),ROW($A1)+100)))

*Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.
-- 
Best Regards,

Luke M
"gyrra" <gyrra@discussions.microsoft.com> wrote in message 
news:EB83BF1A-C7B9-4732-AB7A-94B3781B5405@microsoft.com...
>I am trying to create a simple tool log that also incorporates a sign 
>in/out
> sheet as a second worksheet.
> What I want is to be able to select (not using cut + paste) several rows 
> and
> by simply being selected on "tool list" worksheet, temporarly copied into
> "sign_in" and "sign_out" worksheets.
> I need the data selected from sheet 1 "tools list" to fill rows (starting 
> at
> 20) of the next 2 sheets, and then end user simply prints needed sheet, 
> for
> employee to sign.
>
> This allows me to select only the tools that that employee is checking
> in/out at that time and print that list from a second (or third) sheet 
> along
> with data in previous 20 lines.
>
> I would prefer to be able to keep this workbook protected, but it is not a
> nessessity.
> 


0
Luke
6/4/2010 1:05:58 PM
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"gyrra" <gyrra@discussions.microsoft.com> wrote in message 
news:EB83BF1A-C7B9-4732-AB7A-94B3781B5405@microsoft.com...
>I am trying to create a simple tool log that also incorporates a sign 
>in/out
> sheet as a second worksheet.
> What I want is to be able to select (not using cut + paste) several rows 
> and
> by simply being selected on "tool list" worksheet, temporarly copied into
> "sign_in" and "sign_out" worksheets.
> I need the data selected from sheet 1 "tools list" to fill rows (starting 
> at
> 20) of the next 2 sheets, and then end user simply prints needed sheet, 
> for
> employee to sign.
>
> This allows me to select only the tools that that employee is checking
> in/out at that time and print that list from a second (or third) sheet 
> along
> with data in previous 20 lines.
>
> I would prefer to be able to keep this workbook protected, but it is not a
> nessessity.
> 

0
Don
6/4/2010 8:05:30 PM
Reply:

Similar Artilces:

Switching rows and columns
I am quite sure, in a long forgotten history, Lotus 1-2-3 had an option allowing one to switch column contents to rows and vice versa. I must be looking for the wrong keywords in help and google, can someone tell me how to swap rows and columns in excel? Alternatively, same question for OpenOffice Calc, which I also work with... THANKS! Sh. I don't use OO. But in Excel, you can select your range to transpose. then Edit|Copy Then select the top left cell of the new range edit|Paste special|check Transpose and then ok. You'll have to make sure that there is no overlap between the two...

copying excel 97 Macro to different machine
how can i move a macro that runs under excel 97 to a diff pc running excel 97? regards, antz Hi one way: - open the VBA editor - select the module and export the contents of this module (right-click on the module name) - Import this file on your other machine -- Regards Frank Kabel Frankfurt, Germany Antz wrote: > how can i move a macro that runs under excel 97 to a diff > pc running excel 97? > > regards, > > antz You could copy the workbook that contains the macro to the other pc. Remember that each open workbook has to have a unique name--you can't have two ...

How do I set up an automatic delete or move of a row of data?
I have a job summary that lists the status of my projects. I want to move the data and close up the gaps in the spreadsheet as I change the status to "complete". You need to provide more information. For example, how do you change the status to complete and where do you want to move the data to? Regards Rowan "Zo" wrote: > I have a job summary that lists the status of my projects. I want to move > the data and close up the gaps in the spreadsheet as I change the status to > "complete". I have a list that identifies the "status" of th...

Accessing a second Exchange Mailbox...
I have a Windows 98 SE machine with Outlook 2000 running. I had tried to have my Outlook receive mail from two exchange server mailboxes by using the "additional Maiboxes" section. For some reason it isn't recieving (or checking for that matter) the additional mailbox. Do I have to do something special to get this to work? Thanks in advance, Ian ...

Print heading row on multiple pages
I have to print a sheet of data which contains the data headings on ro 1. However when I go to print a hardcopy (and the data goes beyond page) I cannot find a way to ensure the headings stay at the top o pages 2, 3, 4 etc. A simple problem but one which I can't find an answer to! thanks Greg -- Message posted from http://www.ExcelForum.com Hi goto 'File - Pagesetup - Sheets' and define row 1 as repeating row >-----Original Message----- >I have to print a sheet of data which contains the data headings on row >1. However when I go to print a hardcopy (and the da...

Select top 10 in a list
Hi, I'm looking for a way to search a range in a list and pick the top 10 from that list. Once this has been done, I then need to copy the data to another sheet elsewhere For example, I have a sheet called "MainList" (http:// joemaldon.googlepages.com/), I would like to pick the top 10 from the totals colum and then take each entry and copy into another sheet called "Top10". So, copy ID, Name and Total to sheet called "Top10". Also then add rank positions including the joint places. Can anyone please offer a solution how this can be done please? Note there...

My PC is spamming every 4 seconds
I get a symantec eoutgoing email scan every couple seconds for four emails. Non-stop once the PC us booted up even in safety mode. The problem lies in FICBGIAB.exe. It's a 64b application that I can watch pop in and out of the task manager processes at the same time the emails are being sent. FICBIAB.exe as well as oanjblim.dll (app. ext.) Jpjhdcah.dll (app. ext.) were all created at the same time with several others. I was able to eliminate most, but these three deny me access due it or them being in use or lacking the clearance. It is only my pc I am the admin. They are some ho...

Option Box radio button wont select
I'm must be missing something very simple but... I have an option box in the footer of a continous form. I'll use the option box to set the filter property on the continous form. The problem is the first radio buttton in the group is selected when the form opens and I'm not able to select any of the options. The option group is unbound, nothing in the Control source. Also if I remove the default value of 1 I am not able to select any radio buttons in the group. Thanks, Rick I would create a second option group next to the 1st, make it just basic, ie don't go to fancy ...

how can i copy sms and sender details from mobile phone to pc
I need to retrieve text messages from samsung tocco to pc - os - microsoft office professional 2007 to use as evidence. Any suggestions? Thanks Contact a forensic data expert. Anything you retrieve yourself would be worthless from a legal standpoint. KandM wrote: > I need to retrieve text messages from samsung tocco to pc - os - microsoft > office professional 2007 to use as evidence. Any suggestions? Thanks ...

Print conditional worksheets
I would like to print worksheets depending on the value of B2 (in Sheet1). If B2 = "pens" or "paper" Then print Sheet1, Sheet3 and Sheet4 If B2 = "pencils" Then print Sheet1, Sheet5 Any suggestions on how to do this? Thanks in advance. Hi Try this macro Sub Tetst() With Sheets("Sheet1") If .Range("B2") = "pens" Or .Range("B2") = "paper" Then Sheets(Array("Sheet1", "Sheet3", "Sheet4")).PrintOut ElseIf .Range("B2") = "pencil...

sort and select based on criteria
I have a spreadsheet with the following data, what I would like to do is: 1. sort this in descending order by number of sales (got the macro doing this) 2. Determine the total sales ( a simple SUM in an adjacent cell. (done) 3. Calculate what 90% of total sales are. ( done in another adjacent cell) 4. Determine which parts make up the 90% of sales. (HELP) 5. Graph only the 90% parts. Graph is already created but how do I capture the 90% dataset. (HELP) The idea is to take the entire dataset, sort by number of sales and then graph only those parts that make up 90% of the sales. ...

Copy to new, minus two fields
I'm hoping this is an easy enough question for those with more experience than myself. I am trying to copy all values from a form which has many subforms on it to a new form...this works perfectly with the built in button control "duplicate record." However, I really need two of the fields to be reset to blank, so they will update those two fields for sure. Specifically it's a project update form, and I want all of their updates from the previous week to show up (so they can change only what they need to) but the date begin and date end should ideally go blank, so t...

Setting a row to stay on top
What I would like to know is how to have a row that stays the first row, when I scroll down, so that whatever page I am on, and no matter how far down I am on the page, I can see the subject of the columns I am filling in (for example: name, address, city, state, zip, etc). Any help would be awesome!!! tim Assume your col headers in A1 across Select A2, click Window > Freeze pane -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "timmyc" wrote: > What I would like to know is how to have a row that stays the first row, when > I scroll down, so that ...

Filters
SECOND REQUEST Help! I've tried every filter in Outlooks customize view facility and I still cannot produce a report that: Has all appointments from today forward. I filter "start" "on or after" "today" (or I use the date) but everytime I do - I get all recurring appointments from previous years. I need those items for this year, but not for 2001-2002 & 2003. Is there any way to filter these out? Recurring appointments are a problem since outlook sees them as current appointments if they recurr in the future. If I recall, you can do eithe...

Show all fields from table in select query ?
Hi all. I have 3 tables - INVENTORY, IN and OUT I made a select query to calculate the quantity of inventory on stock, but I only see the inventory items that are in both IN or OUT tables. If I select to show all fields from Inventory and only those related from other tables a get null values. Is there a way to have a field showing 0 (zero) instead of empty field ? You can use an outer join instead of an inner join (the default.) Details in: The Query Lost My Records! (Nulls) at: http://allenbrowne.com/casu-02.html A better solution might be to combine the IN and OUT into one...

Copy part of text file
Hello I am trying to open a CSV file (File*.*) and copy a portion out of that file into my work book. It runs up to the point of opening the files. What is wrong with my code? rnum = 1 Fnum = 1 MyPath = "C:\path\file" If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\" End If FilesInPath = Dir(MyPath & "File*.*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If On Error GoTo CleanUp Set basebook = ThisWorkb...

Excel 2003: In a Macro,how to select a variable row length table
I wish to create a macro that will perform functions upon multiple rows of data. The number of rows of data will change every time. Problem : I create a macro by recording. I select rows of data using CTRL/down arrow on the last column and then selecting back to the first column of the first data row. This appears to 'hard code' the number of rows of data to be the number of rows that exists at the time of macro creation. When I run the macro another time it then selects the same number of rows as when the macro was created, which will then not match the new current data. i...

OL2003: Selecting Multiple "Blocked Senders"?
In Outlook 2000, I was able to Ctrl-click multiple messages, then click on "Junk Senders List" and all the senders would be added to my spammers file. Under OL2003, I can't figure out if it's possible to designate more than one message at a time to be added to the Blocked Senders list. Is it? If so, how? {Jonathan} ...

rows
can you get more than 64536 rows in excel 2000? Thanks Barry Sure can. You may have 65536 -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "barry" <barrykuon@yahoo.com> wrote in message news:00d601c3470f$c8a9ab30$a501280a@phx.gbl... > can you get more than 64536 rows in excel 2000? > Thanks > Barry Use the 1000 rows you have left over between 64536 and 65536? Other than that, there is no more. On Thu, 10 Jul 2003 11:19:21 -0700, "barry" <barrykuon@yahoo.com> wrote: >can you get more than 64536 rows in excel 2000? >Th...

Sharing excel worksheets
Is there any way possible that I can put three separate Excel spreadsheets on three separate computers and if one makes a change on any one of the spreadsheets and saves it, that it replicates on the other two. The same changes happen on the other two? Hi use 'Tools. Share workbook' Note: This feature allows only a restricted set of feature. Check the Excel help for more details -- Regards Frank Kabel Frankfurt, Germany "tthomas@lcor.com" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0a0c01c4b2bb$c5444270$a301280a@phx.gbl... > Is there an...

How do I link data between worksheets within a workbook?
I want to pull data from one worksheet to use in a formula in another worksheet. todo4u, just ref. the sheet name, like this, in a cell in sheet1, will add whats in sheet1 and sheet2 cell A1 =A1+Sheet2!A1 -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "todo4u" <todo4u@discussions.microsoft.com> wrote in message news:7F287A45-B19F-4AC1-A2F4-285ED945B28C@microsoft.com... > I want to pull data from one worksheet to ...

Populating second sheet
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I would like to keep an item price list in sheet1 and leave a blank column for order qty. Then in sheet2, I would like to create an invoice for all items with a qty greater than zero in sheet1. I want to pull all item# and the price for all rows with a quantity. ...

in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to the newly created copy of this sheet?
in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to the newly created copy of this sheet? Hi Daniel, Try something like: Dim WB As Workbook Dim WS As Worksheet Set WB = ActiveWorkbook With WB .Sheets("Mysheet").Copy Before:=.Sheets(1) End With Set WS = ActiveSheet --- Regards, Norman "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:OdVZVvmgFHA.3088@TK2MSFTNGP10.phx.gbl... > in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference > to > th...

faq: How to obtain the select plain text?
Hi, Just like the notepade.exe in windows, I type some plain text and select it using mouse with left button pressed, and copy it to the clipboard by select copy command in the menu Edit. How to do that? Thanks very much. http://msdn2.microsoft.com/en-us/library/80db3kax(VS.80).aspx http://www.codeproject.com/editctrl/editctrltutorial.asp Also, take a look at CEdit::Copy() CEdit::Paste() CEdit::Cut(). They should do what you'd like. http://msdn2.microsoft.com/en-us/library/75bh1f1t(VS.80).aspx Tom "fcvcnet" <fcvcnet@163.com> wrote in message news:fhb5as$ghh$1@ne...

Selecting a namespace-prefixed node
Hi I am pretty much an xml beginner; hopefully someone can easily answer this one... I want to select a node representing a worksheet in an xml document for the Office Web Components spreadsheet. The file uses xml namespaces, of which I know very little. But it does seem clear that I can't select the <ss:Worksheet> element by name only; the xpath "//Worksheet" does not match any nodes Extract from the file, in case this is needed to come up with the solution <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"><!-- ... there's more, but...