?
Solved

move and append data from one sheet to another based on criteria

Posted on 2013-01-27
15
Medium Priority
?
241 Views
Last Modified: 2013-01-29
I have a worksheet with project information.

There are 8 columns of information with the last column being "Status"

I have another worksheet that will show only completed projects.

When the status on the Project worksheet is changed to "Completed", I want to move and appended the data to the Completed Projects worksheet.
0
Comment
Question by:mnoisette
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 38824668
can you post a sample workbook as you did not mention how many rows in the project and whcih rows will show Completed.
gowlfow
0
 

Author Comment

by:mnoisette
ID: 38824705
Not a problem...

The SUMMARY worksheet needs to contain a rollup of data from the following worksheets:
GOER DIVISION, HUMAN RESOURCES, LEEDS DIVISION, MISCELLANEOUS, and
SOFTWARE PROJECTS

The COMPLETED worksheet should only contain data with the Status of Completed from the above mentioned worksheets.

Thanks.
Project-Management.xlsx
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38824907
where is the data !!!
I said we don't know how many rows !!!
pls put some sample data as like this it does not help and you will not get the desired result.
gowflow
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:mnoisette
ID: 38824967
Here Ya go...

Sorry about that...

This file also has a macro that will add the tab name to a cell.
So it is a .xlsm file this time.

Thanks.
Project-Management-Test.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38825724
ok so here are some more quesitons:

1) when do we transfer ? when the line is complete or the whole project is complete ?

2)what do we do with the diffrent sheets ?? Now they all show ame info do we transfer each and every completed line of each and every worksheet to the completed worksheet all following each others ??

3) Like you want to archive and clean ? is that what you are aiming at ?
archive in completed and clean from actual worksheets ?

4) Last but not least do you want the moving to be done 'automatically' like the second the status turns to Completed .... the record is moved rightaway ? or you want a button that you can activate whenever you feel like and it will flush all the completed ??

gowflow
0
 

Author Comment

by:mnoisette
ID: 38826756
Answers:

1) When the line is marked complete, not the whole project would be fine.

2) Transfer each and every completed item from each of the sheets to the Completed sheet.

3) Cleaning of the Completed sheet can be done manually.

4) A button to flush the completed items from the Summary sheet will be fine.

Thanks!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38826987
Well I am having a problem with your function can we disable it temporarely so I ran my macro and then we see how to fit it as it is giving conflicting info. I don't like these functions that get in the way !!! :)

Also, what do you want to do with the rows that are copied ? delete them ?? like suppos you have

20 .... Completed
21 ...  Completed
22 ...  Wait
23 ...   In process
24 ...  Completed
25 ....  Working


you want to have in that worksheet a compression like:
20 ...  Wait
21 ...   In process
22 ....  Working

or you want to have blank rows like:
20 ....
21 ...  
22 ...  Wait
23 ...   In process
24 ...  
25 ....  Working

Anyway check this version on sheet COMPLETED activate the button and see what it does. For now I have not done anything to the original data in the sheets waiting your reply. but at least check if data is ok as it is transfered. I have diabled you function as it was going waco and naming all the companies to 1 name with weired errors. Will check this later.

gowflow
Project-Management-Test.xlsm
0
 

Author Comment

by:mnoisette
ID: 38827485
That works fine!!!!
Disable or you can get rid of my macro all together. The data in the Project Type column can be entered manually. Thanks
0
 

Author Comment

by:mnoisette
ID: 38827532
Also, Yes. The rows that are copied go ahead and delete tham.
Thanks
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 38827729
ok here it is.
Check it out and let me know.
gowflow
Project-Management-Test.xlsm
0
 

Author Comment

by:mnoisette
ID: 38829102
That works wonderfully!!

Would the process be almost the same to have a button that copied everything that did not have the status of "Completed" to the "Summary" sheet. Excluding the the items on the COmpleted worksheet?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38829940
you mean all the balance of the items in the sheets to be copied onto summary ?
whay would you do that then ? Have 1 summary and 1 completed at the first place ?
gowflow
0
 

Author Comment

by:mnoisette
ID: 38830864
I agree with you. It's what my boss asked for.
MS Project is what she needs but you have to do what you're asked to do.
I added a Pivot Table to the Summary sheet to roll up everything so it should be good.
Everything worked wonderfully!
Thanks!
0
 

Author Closing Comment

by:mnoisette
ID: 38830885
Everything worked wonderfully!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38831246
Your welcome ! and if you wanted it in a macro it is no big deal just let me know if you post an other question for that and I will be glad to assist.
gowflow
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question