OfficeFrustration

OfficeFrustration (http://www.officefrustration.com/index.php)
-   New Users (http://www.officefrustration.com/forumdisplay.php?f=7)
-   -   copy selected rows to second worksheet (NOT Cut + Paste) (http://www.officefrustration.com/showthread.php?t=1119317)

gyrra June 4th, 2010 12:01 AM

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.


Luke M[_4_] June 4th, 2010 02:05 PM

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.




Don Guillett[_2_] June 4th, 2010 09:05 PM

copy selected rows to second worksheet (NOT Cut + Paste)
 
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

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




All times are GMT +1. The time now is 04:50 AM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
OfficeFrustration.com