Bringing data together from different sheets as it is added

[Andrew1234]

Hi all,

I have a large spreadsheet that contains mechanics maintenance records. there is a section at the bottom of each vehicle page where the mechanic will type the date, millage, work done, parts invoice number and which mechanic carried out the work. There are multiple vehicle pages on one sheet but the user can only see one at a time as zoom is set at 120%, they are navigated through using many hyperlinks.
What I now what to do is have one master list of all work done.

Apologies for the poor image. I could'nt upload any larger file.
But this shows how each page is laid out showing the information at the bottom. to the right and left of this are many more pages of vehicles. what i would want is to try and do is take the information added in these cells and have them go into one list so it is easy to see what work has been done to which vehicles on which day by what mechanic.

I must also add, there is another similar workbook which lists machinery maintenance and i would like the information added into this sheet to also be added to a master list for both workbooks. Ideally this master list would be on a separate document but if this is not possible then a new sheet on the two workbooks will do well also.
If it is possible i would like to stay away from VBA but if we must use it this is ok.

OBJECTIVE: when information is added on a vehicle page i would like it to added to a master list of work done. Master list must include the vehicle registration, work done, date, and mechanic.

I am out of my depth and really not sure how to go about achieving this and have been researching methods for a few weeks now. If anyone could help it would be greatly appreciated.

Many thanks to all in advance.

A.

 

[OaklandJim]

Here is the challenge regarding your request for assitance. Considering what to do for you is difficult in the abstract. And, without having the workbooks to scope out a solution a person helping you would need to create dummy data to work with. I cannot promise anything but I'd be willing to attempt to assist if a working copy of the workbooks are made available. You can provide a link to both using dropbox or 1drive.

 

[Andrew1234]

Thank you for your reply. I understand, I will create dummy data and try send it.
Many thanks

 

[Andrew1234]

@OaklandJim, here is a dropbox file link.
fake data for excel help.xlsx

It shows you what the mechanics have to work with.
The file only has the VANS sheet on it, but all of the other sheets have the exact same layout. And then there another work book for heavy plant with the same layout again.

if you open the file in excel and have zoom at 120% and formula bar and heading and ribbon hidden then you will see the full page when hitting hyperlinks.


If you have any questions do ask.
Thank you for your help

 

[OaklandJim]

I think that I understand your need. First as far as I can tell you will need a VBA solution.

But here is a key issue FOR ME: having all the vehicles for a category (e.g. Vans) on one sheet. That makes your request challenging if not impossible FOR ME to accomplish. Specifically, without vehicle-specific sheets I cannot think of a way to identify the vehicle being processed. If using separate sheets the tab name could be the vehicle ID.

If it is doable to have separate sheets for each vehicle then a somewhat basic sub could be set up to update the master list as you want. Update would be accomplished by user when they click a button -- whose caption is "Update Master" or "Done" -- one for each sheet.

Not too big of a deal to use a separate workbook for the Master but one thing at a time.

If you do go to one sheet per vehicle you could have a "Start" sheet at the beginning of the workbook that has a hyperlink to each vehicle sheet. User clicks on the hyperlink and is taken to the respective sheet (for the vehicle). Other sheets are then hidden so there is only one vehicle sheet showing at a time. You could group the hyperlinks for each vehicle category on the start sheet.

It occurs to me that adding or deleting vehicle sheets would be better than adding or deleting from one sheet anyway.

That is the best that I can come up with for now.

 

[Andrew1234]

@OaklandJim Thank you for your advice and your time. As splitting it up into individual sheets would take a long time I think I will just do without the master list of work done. The current layout as seems to be working ok at present. So I couldn't justify spending a lot of time in trying to create a master list.
But again, thank you for your time.

Regards,
Andrew