Forum Discussion

georgekennedy's avatar
georgekennedy
Copper Contributor
Oct 22, 2023

EXCEL HELP

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

excel
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Oct 23, 2023
    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.
    • georgekennedy's avatar
      georgekennedy
      Copper Contributor
      Oct 24, 2023

      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.

       

Resources