EXCEL HELP

[georgekennedy]

Hi guys,

First-ever question to the tech team.

I work in a small body repair shop that deals principally with a vehicle rental company and looking to cut back on paper use by putting the works process into Excel.

Unfortunately, I put myself in the ‘limited’ bracket when it comes to utilizing all the toys available in Excel.

I’ve attached the 4no. images to show what I’m trying to put together but need help.

Thanks in advance.

Some background.

1. I’ve linked cells in ‘Drivers In’, ‘Live Jobs’, and ‘Drivers Out’ whereby typing in details, ‘Depot/Registration/model/comments’ when typed in ‘Drivers In’ automatically appear in the relevant columns in ‘Drivers Out’.
2. I’ve also linked cells in ‘Drivers In’ and ‘Live Jobs’ for  ‘Registration and Model’.
3. There are linked cells in place from ‘Drivers In/Live Jobs/Drivers Out’ to ‘Completed Works’ … Refer to column headings.

What I’m trying to achieve.

1. When works are completed, (by inputting Handover Date/Time) in the ‘Drivers Out’ sheet, the complete row across all three tabs (Drivers In, Live Jobs, Drivers Out) is automatically moved to the completed works sheet, leaving only ‘incoming’ and ‘Live Jobs.’ Note: if possible I’d prefer if it could be moved to another (separate) file. Regarding the ‘Completed Works’ sheet, the parts list (drop-down menu) when clicking the ‘Click for parts’ has not been carried across in the paste link from the relevant cell in ‘Live Jobs’. Is there a way around this? Presently it’s the ‘Data-Data validation’ route I’ve gone down. Editing the parts list is required.
2. I’m looking for new works/works in progress in the ‘Drivers In/Live Jobs/Drivers Out’ to be sorted by ‘Estimated Completion Date’  (ECD in ‘Live Jobs) by re-arranging each row (job) in order of earliest to latest date. This can change daily/hourly if we are dependent on parts delivery. Can this be done? Also, is there a way of having the completion date in day & date format?
3. Is there a way of removing ‘0’ from cells that have not been populated?
4. Finally, will I be able to add additional sheets to cater for other workshop functions which will be standalone?

1 of 42 of 43 of 44 of 4

[#2]

in the ‘Drivers Out’ sheet, the complete row across all three tabs (Drivers In, Live Jobs, Drivers Out) is automatically moved to the completed works sheet,

If I were you, I would like to save all datas in one sheet which can apply filter to other relative sheet or view rather than save to different sheets at first.

[#3]

@peiyezhu 

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.

[#4]

https://answers.microsoft.com/en-us/msoffice/forum/all/converting-a-sumifs-to-an-array/5c7f3fab-52bc...
https://techcommunity.microsoft.com/t5/excel/how-can-i-check-yes-for-x-values-in-multiple-columns-in...
https://techcommunity.microsoft.com/t5/excel/excel-vba-to-filter-a-table-based-on-multiple-search-cr...


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.

[#5]

@peiyezhu 

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.

[#6]

@peiyezhu 

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.

[#7]

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?

[#8]

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.

[#9]

@georgekennedy 

Your column AC is text rather than datetime number,causing you can not just format them as time format which you expected.

Why do you need change the format?

Do you need further calculation based on this column？

[#10]

@peiyezhu 

My apologies for the delay in reply and for being a nuisance.

The worksheet has been revised, please find attached, Live and Completed Works (MS team).

1. Extra Columns, the last column is now ‘AD’
2. Yellow highlighted rows in ‘Live Works’ are complete.
3. These (yellow highlighted) rows I’m looking to be automatically transferred to ‘Completed Works’ with ‘white/no fill rows’ 7 & 13 left in ‘Live Works’, with both rows being moved to the top of the file (Live Works).
4. The auto transfer function will happen when the relevant cell in column ‘AD’ (Live Works) is populated with the ‘Handover Date/Time’. This action completes the job.
5. With regards to your last post, I’d merely been reading that this date/time (column AD) may have to be displayed in “dd/mm/yyyy” format. I apologise for misleading you.
6. As I’d mentioned in my initial request, I’m limited in terms of the functions available in Excel.
7. If by merely populating cell ‘AD’ activates the file transfer, (that is no need for a formatted date/time) this is what I would be looking for.

Once again, thanks in advance for your time and patience

[#11]

@georgekennedy 

convert live works to Table,them apply.filter on sheet completed works.

=FILTER(表1,表1[HANDOVER DATE/TIME]<>"")

In this case,using sql.to filter also an option.

See attached in detail.