Home |
Search |
Today's Posts |
#1
|
|||
|
|||
cut and paste to different worksheet
Hi
Experts I am working on excel sheet to make a daily production report. In my worksheet, there are 300 rows selected for a week and column from A to W. Column F is named as "STATUS". When a particular job finishes, person on the section puts C (C means Completed) in the column F. What I am after is as soon as column F (Status) goes C, then information for that job from column A to column W should get copied automatically to another worksheet 3. consider in one day 10 jobs gets completed onto a section then 10 jobs infomation from column A to column W should get copied automatically onto worksheet 3 as soon as status (column F) gets updated to "C". Any workable suggestion will be greatly accepted. Thanking in anticipation!!! |
#2
|
|||
|
|||
One play to try ..
Assume source table is in Sheet1, cols A to W, data from row2 down (Key column is col F, key input in col F is: C) In an empty col to the right, say col X Put in X2: =IF(F2="","",IF(F2="C",ROW(),"")) Copy X2 down to say, X100 to cover the max expected data range for the source table (can copy down ahead of expected data input) (Leave X1 empty) In Sheet3 ------ Paste the same col headers into A1:W1 Put in A2: =IF(ISERROR(SMALL(Sheet1!$X:$X,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA LL(Sheet1!$X:$X,ROWS($A$1:A1)),Sheet1!$X:$X,0))) Copy A2 across to W2, fill down to W100 (cover the same range as was done in col X in Sheet1) Format the cols as per the source cols in Sheet1 Sheet3 will return the desired results, i.e. only the rows from the source table where the status input in col F in Sheet1 is: C, all bunched neatly at the top Adapt to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "MINAL ZUNKE" wrote in message ... Hi Experts I am working on excel sheet to make a daily production report. In my worksheet, there are 300 rows selected for a week and column from A to W. Column F is named as "STATUS". When a particular job finishes, person on the section puts C (C means Completed) in the column F. What I am after is as soon as column F (Status) goes C, then information for that job from column A to column W should get copied automatically to another worksheet 3. consider in one day 10 jobs gets completed onto a section then 10 jobs infomation from column A to column W should get copied automatically onto worksheet 3 as soon as status (column F) gets updated to "C". Any workable suggestion will be greatly accepted. Thanking in anticipation!!! |
#3
|
|||
|
|||
hi
Max Thnx for your suggestion. I tried your solution. I think you gave me correct solution but I didn't tell you that in my worksheet 1, I have VB script and conditional formatting which evaluates if I put some variables in cells. First of all, information in columns from A to E gets filled automatically, If I run query developed in MS ACCESS. I have recorded macro for that and allocated a button to it. Say I have data from A4 to E4 A104 to E104 (filled in automatically after running macro) In column B heading is "Customer Name" and (B4 to B104) change colour depending on the customer name. In column D heading is "Due Date" which has 3 condition in it based on "if function" and changes colour if evaluates true. column F is what I am trying to utilize as Key column for my trick. column G has VB script and cnditional formatting related to it. Column H gets filled up automatically depending on the result of the column G. column I and J have again VB script and conditional formatting related to them. column K gets filled automatically depending on result of column I & J. column L & M have VB script and conditional formatting related to them. column N gets filled automatically depending on result of column L & M. column Q conditons is based on the result of column G & column(I or J) & column (L OR M). So your solution is not working as it should have. What I was thinking to write VB script something like that If( Target . Column = 6) then (here A=1, B=2, C=3, ...., F=6, ....., Z=26) if Target. Value = "C" then Select entire Row( I don't know how to select that entire row using VB commands) select cut ( I think like Selection.Cut) make sheet 3 active ( don't know) select next available blank row(don't know) select paste (selection.paste) save sheet3 (don't Know) make sheet 1 active ( don't know) save sheet 1 as well (don't know) If you can help me I will be very thankful. Thanking you in anticipation!!! |
#4
|
|||
|
|||
You're welcome. But I'm not able to help you with the vba Qs. Do hang around
awhile for possible insights from others to these. Or, consider putting in a fresh post in .programming. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
thnx max for your efforts
cheers "MINAL ZUNKE" wrote: Hi Experts I am working on excel sheet to make a daily production report. In my worksheet, there are 300 rows selected for a week and column from A to W. Column F is named as "STATUS". When a particular job finishes, person on the section puts C (C means Completed) in the column F. What I am after is as soon as column F (Status) goes C, then information for that job from column A to column W should get copied automatically to another worksheet 3. consider in one day 10 jobs gets completed onto a section then 10 jobs infomation from column A to column W should get copied automatically onto worksheet 3 as soon as status (column F) gets updated to "C". Any workable suggestion will be greatly accepted. Thanking in anticipation!!! |
#6
|
|||
|
|||
You're welcome !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "MINAL ZUNKE" wrote in message ... thnx max for your efforts cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|