If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
copy selected rows to second worksheet (NOT Cut + Paste)
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)+100COUNTIF('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)+10 0))) *Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M "gyrra" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|