Forum Discussion
EXCEL HELP
Thanks for your reply.
Given your thoughts, I’ve had a rethink and now have two worksheets for which I have attached 4 images, Live Works (1 of 4, 2 of 4) and Completed Works (3 of 4, 4 of 4).
Both worksheets are listed columns A to Z, with each row being an individual job.
The ‘Live Works’ worksheet is for current ongoing works.
The ‘Completed Works’ worksheet will list all completed works.
As mentioned previously, on completion of each job in ‘Live Works’, I’m looking for that complete row being automatically sent to the ‘Completed Works’ sheet and at the same time being removed from the ‘Live Works’ sheet.
In this example, jobs 1,3,4 & 8 (Live Works) are complete and have been moved to the ‘Completed Works’ worksheet.
Either manually or, a preferred automatic solution, these jobs would be removed from the ‘Live Works’ worksheet.
Also, if there is a possible automatic solution, as a for instance, if job 2 (Live Works) were to be the next job to finish, on moving to the ‘Completed Works’ worksheet, would it be placed in row 7 (Completed Works)?
This process would be actioned by populating the relevant cell, column Z (‘Live Works’) with handover date and time.
I’m aware of a manual process, Control/X followed by Control/V. However, I must be missing something as each time I carry out this process, using these spreadsheets as an example, on moving a complete row from ‘Live Works’ to ‘Completed Works’, I lose the formatting on that row in ‘Live Works’.
I’ve been all over the internet looking for a way forward (to automatically move rows between these worksheets) and repeatedly I’m directed to an Excel VBA solution.
If that is indeed the case, it’s way above my excel expertise.
Thanks in advance.
https://answers.microsoft.com/en-us/msoffice/forum/all/converting-a-sumifs-to-an-array/5c7f3fab-52bc-45cc-9262-f2f1d1f9bb37
https://techcommunity.microsoft.com/t5/excel/how-can-i-check-yes-for-x-values-in-multiple-columns-in-excel/m-p/3894703#M200728
https://techcommunity.microsoft.com/t5/excel/excel-vba-to-filter-a-table-based-on-multiple-search-criteria/m-p/3812945
sorry, maybe I can not understand you well.
I use sql and database to process data analysis.
And in your situation,You can refer to filter funtion.
If you upload your workbooks with some data and expected result rather than pictures only,I think more brothers will take part in the discussion.
Hi Again,
In response to an earlier request, please find attached:
Copy of ‘Live Works’ sheet.
Copy of ‘Completed Works’ sheet.
As mentioned previously, the purpose of the ‘Live Works’ sheet is to coordinate the day-to-day running of our workshop.
The purpose of the ‘Completed Works’ worksheet, is, as the title suggests to store all completed works, possibly to the end of time.
This is where I need help.
I’m looking for a way of moving all data for a completed job (contained in a single row) from the ‘Live Works’ worksheet to the ‘Completed Works’ worksheet.
At the same time, the data is removed from the ‘Live Works’ worksheet.
If you look at the ‘Live Works’ sheet, I have highlighted rows 3,4,7,9 (representing four individual jobs) in yellow.
As an example, I’d be looking to move these rows from the ‘Live Works’ sheet to the ‘Completed Works’ worksheet.
This command/trigger (to move all data between worksheets) would be on entering the ‘Handover Date & Time' in column ‘AC’ of the ‘Live Works’ worksheet.
When the data is moved to ‘Completed Works’, the remaining rows (5,6,8,10) in ‘Live Works’ are rearranged to, in this case, rows 3,4,5,6.
I hope this is clear.
Thanks in advance.
- https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
looks filter is ok to separate one sheet to sheet_complete.and sheet_in_process according to column R.
3,4,7,9 (representing four individual jobs) in yellow
R8=complete?
not yellow?- R8 should be yellow.
Sorry, I was in a bit of a rush.
I've been thinking since I sent files.
I am looking to activate the move from 'Live Works' to 'Completed Works' when the date and time is entered in column 'AC'.
Would changing the date & time format to "DD/MM/YY HH:MM" in column 'AC' make this possible?
Once again thanks in advance.
Again, thanks for your prompt reply.
I'm out all day (United Kingdom Time), but will look to attach actual files when I return later on today as I'll need to populate the cells with working examples/information.
Thanks again.