Move completed rows to another sheet, and parts need to the bottom sheet.

**sherryp5165** · 07-27-2012, 02:15 PM

Hello!!
I am trying to create this spreadsheet and am not very familiar with macros at all or if it is possible to do this not using a macro at all. I am using Excel 2003, I would like to move rows that are completed in the Daily schedule to the completed worksheet based on the information in column O (completed yes or no). I would then like to move rows to the bottom of the Daily schedule based on the information in column T and highlight it as well. I created drop down list but am unsure that this would make it possible for me to do this or affect what I need. Any ideas please help. I have attached the spreadsheet

Thanks !!!
Sherryp

**jaslake** · 08-04-2012, 09:45 PM

Hi Sherryp

Is this the correct file? I don't see "Daily Schedule" or a "completed worksheet" or Column O (completed yes or no) or any information in column T. What am I missing?

**sherryp5165** · 08-08-2012, 10:53 AM

My apologies, it would be helpful if I attached the correct file. But it keeps telling me my file is to large, is there another way I can send this?

**jaslake** · 08-08-2012, 11:57 AM

Hi Sherryp

zip the file then attach the zipped file.

**sherryp5165** · 08-09-2012, 08:06 AM

Thank you!!
Please see the attached file.

**jaslake** · 08-09-2012, 10:27 AM

Hi sherry
What in Column T determines this

I would then like to move rows to the bottom of the Daily schedule based on the information in column T and highlight it as well

Does the entire row get highlighted? Does the row get moved yet again and is the highlight removed if Column T gets changed again?

What's with the empty rows in your Daily Schedule (rows 20 through 100) and the second set of different headers in row 101.

**sherryp5165** · 08-09-2012, 10:42 AM

If column T states Yes I would like to move it to the last row that is empty below the list of data entries above it. Does that make sense? And highlight that entire row (any color) to represent that parts are needed for this line.

The empty rows are what is seperating the information for my data validation drop down list at the top of the worksheet. Maybe I did not do this right but was not sure how to create the list or source data in another worksheet. I could not get it to do this.

I hope this makes sense.
Thank you for your help.

**jaslake** · 08-09-2012, 11:08 AM

Hi sherry
Do you want Blank in your Data Validation Lists as you have now?

**sherryp5165** · 08-09-2012, 12:20 PM

Will blank remove my data? If so, I would rather have the macros to work with the spreadsheet then have the drop down lists in the spreadsheet. I just learned how to input the data validation lists so I am not advanced in the knowledge of this.

**jaslake** · 08-09-2012, 12:28 PM

Blank in the Data Validation List will allow the User to select "Blank". It won't affect the data in any way.

**sherryp5165** · 08-09-2012, 12:31 PM

Oh ok sounds great.

**jaslake** · 08-09-2012, 05:05 PM

Hi sherry

Had to step away for a couple of hours.The code in the attached has two procedures:

Move_to_Complete(use CTRL + o to run this macro)
This procedure filters Column O of Daily Schedule for all records with "Yes" in Column O and copies those records to Completed. It then removes those records from Daily Schedule.
Parts_Required (use CTRL + t to run this macro)
This procedure filters Column T of Daily Schedule for all records with "Yes" in Column T and moves those records to the bottom of Daily Schedule and colors those rows yellow (I think...I'm color blind).

The procedures can be run in any order. I'd suggest running them on the attached sample file first. Assuming they work (they've been thoroughly tested) then run them on a COPY of your live file.

Please note that I've removed your Data Validation Lists from Daily Schedule and placed them in their own sheet called Lists. Please also note that I've added several Dynamic Named Ranges to your workbook that contain the Validation tables. Dynamic Named Ranges change as you add items to the Named Range. You may wish to look at Dynamic Named Ranges. Here are two links that I've used in the past.

http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm

Let me know of issues.

Please Login or Register  to view this content.

**sherryp5165** · 08-10-2012, 07:42 AM

This may be a silly question, but will this allow me to insert and delete rows without affecting the macros?

**jaslake** · 08-10-2012, 07:57 AM

I should think so.

**sherryp5165** · 08-10-2012, 08:39 AM

I want to thank you for all your help, may I please ask for something else? If so, can I set this up to auto fill once I start typing?

**jaslake** · 08-10-2012, 10:24 AM

You can but it's ugly...see this link
http://www.ozgrid.com/Excel/autocomplete-validation.htm

On the other hand I'll look at this link
http://www.vbaexpress.com/kb/getarticle.php?kb_id=244

There's also this link which I've adapted a couple of times...it's also a bit squirrley
http://www.contextures.com/xlDataVal10.html

**jaslake** · 08-10-2012, 04:08 PM

Hi Sherry
Do you have access to Excel 2007?

**sherryp5165** · 08-13-2012, 04:49 PM

Ok did not do the autofill as of yet, the word UGLY scared me. However, I did test the ctrl 0 and ctrl t for the automatic row/lines to move. AWESOME!!!!! THANK YOU SO MUCH FOR YOU HELP!!!!

**jaslake** · 08-14-2012, 07:55 PM

Hi Sherry...still working on it...do you have access to Excel 2007?

**sherryp5165** · 08-15-2012, 08:52 AM

Unfortunately I do not have access to Excel 2007, they only have the 2003 version.

**jaslake** · 08-15-2012, 12:29 PM

Hi Sherry
I'll keep playing around with it. I can make it work as I wish in Excel 2007. I can make it work but NOT as I wish in Excel 2000. I don't have Excel 2003 but my Son does. I'll see what I can do.

**jaslake** · 08-18-2012, 11:25 PM

Hi Sherry
I received in the mail today my Son's Excel 2003 Disk (he's on a MAC and not longer uses Windows based software) so, I believe I'm legitimate in using it (we'll see). In any event I believe I can make this work as I want it to work in Excel 2003. I need to test it further...I'll get back to you.

**jaslake** · 08-19-2012, 12:46 PM

Hi Sherry

The code in the attached has been developed in Excel 2003 and appears to work as I envision it. The code is adapted from here

There's also this link which I've adapted a couple of times...it's also a bit squirrelly
http://www.contextures.com/xlDataVal10.html

and gives you this ability

can I set this up to auto fill once I start typing

in Columns D, G, H, K and S.

Play with it a bit...let me know your thoughts.

**sherryp5165** · 08-20-2012, 04:31 PM

Thanks yes it is a little squirrly. Going to work with it some more.

**jaslake** · 08-20-2012, 05:48 PM

Hi Sherry

Depending on who's using this (just you or one or more other users) you may wish to explore the use of a UserForm to enter the data into your Daily Schedule worksheet. It's much cleaner and is rather simple to maintain. I've developed several of these such things...it's "like" a database system. One of the things you'd probably need to add to Daily Schedule is some type of Serial/Incident/Job/Repair/Request Number. I've gotta believe you already have this...if not...

Play with what you have...keep me posted

**sherryp5165** · 08-22-2012, 11:08 AM

Question- With this particular spreadsheet I have a column for response time- I can not seel to locate the formula to create a response time for days and hour/minutes. Any ideas??

**jaslake** · 08-22-2012, 01:53 PM

Hi Sherry
Don't know but if you'll give a few examples of what you have and what you'd like it to be I'll play with it.

**sherryp5165** · 08-23-2012, 10:36 AM

Attached is the file that you worked on for me which works great, I am trying to locate the formula to calculate response time for column u. This would be calcualted utilizing columns E, F and P. I am thinking that in order for this to work I would need to have a date of arrival next to column p for this to work and that's why it won't work. I hope this makes sense.

**jaslake** · 08-23-2012, 11:05 AM

Hi Sherry
Check out this link http://www.cpearson.com/excel/datearith.htm
Look at Time Intervals

I placed this formula in Cell P5

Please Login or Register  to view this content.

and got 22.0666...

Sorry should have said "placed this formula in Cell U5"...

**sherryp5165** · 08-24-2012, 03:25 PM

WOW!!! Perfect thank you again!!!!!

**jaslake** · 08-24-2012, 04:20 PM

Hi Sherry
You're welcome. If this resolves your issues please mark your Thread as SOLVED.

**Cutter** · 08-24-2012, 05:34 PM

@ sherryp5165

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Move completed rows to another sheet, and parts need to the bottom sheet.

LinkBack

Thread Tools

Rate This Thread

Display

Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Re: Move completed rows to another sheet, and parts need to the bottom sheet.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions