• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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

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
mnoisette
Asked:
mnoisette
  • 8
  • 7
1 Solution
 
gowflowCommented:
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
 
mnoisetteAuthor Commented:
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
 
gowflowCommented:
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.

 
mnoisetteAuthor Commented:
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
 
gowflowCommented:
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
 
mnoisetteAuthor Commented:
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
 
gowflowCommented:
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
 
mnoisetteAuthor Commented:
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
 
mnoisetteAuthor Commented:
Also, Yes. The rows that are copied go ahead and delete tham.
Thanks
0
 
gowflowCommented:
ok here it is.
Check it out and let me know.
gowflow
Project-Management-Test.xlsm
0
 
mnoisetteAuthor Commented:
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
 
gowflowCommented:
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
 
mnoisetteAuthor Commented:
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
 
mnoisetteAuthor Commented:
Everything worked wonderfully!
0
 
gowflowCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now