Solved

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

Posted on 2013-01-27
15
234 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
  • 8
  • 7
15 Comments
 
LVL 29

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 29

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
 

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 29

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 29

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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 29

Accepted Solution

by:
gowflow earned 500 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 29

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 29

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now