[Solved] Help figuring out data tables

[ambern1023]

I need to create an excel workbook that is will sheets for all of our vehicle's maintenance, parts purchased etc. then 1 workbook that keeps full historical info of all vehicles, then another one for services to "check regular with conditional highlighting. I currently use a Microsoft form to get info to a spreadsheet, so I need it to be linked to that workbook so when new entries come in it auto populates the info to the main linked workbook and then ideally to the appropriate vehicle sheet in that same workbook and well as to historical.
I'm not sure how to make it go to the check regularly i may have to do those updates myself,

we purchased ALOT of parts for our vehicles as it is heavy truck with lots of attachments, so we need to keep track of the parts purchase with part #, cost and vendor purchased from by vehicle as well.

what is my best approach to this to track service history, parts, needed repairs, and history of all services? We have 7 Vehicles now.

Also it appears when using excel online (we use Dropbox i am getting a notification that links to other workbooks are not supported online. What should I be using as my boss prefers us to use the online versions through our Dropbox to prevent conflicted copies. however not all features are available with online,
what do i do here?

 

[nafissoumik53]

I need to create an excel workbook that is will sheets for all of our vehicle's maintenance, parts purchased etc. then 1 workbook that keeps full historical info of all vehicles, then another one for services to "check regular with conditional highlighting. I currently use a Microsoft form to get info to a spreadsheet, so I need it to be linked to that workbook so when new entries come in it auto populates the info to the main linked workbook and then ideally to the appropriate vehicle sheet in that same workbook and well as to historical.
I'm not sure how to make it go to the check regularly i may have to do those updates myself,

we purchased ALOT of parts for our vehicles as it is heavy truck with lots of attachments, so we need to keep track of the parts purchase with part #, cost and vendor purchased from by vehicle as well.

what is my best approach to this to track service history, parts, needed repairs, and history of all services? We have 7 Vehicles now.

Also it appears when using excel online (we use Dropbox i am getting a notification that links to other workbooks are not supported online. What should I be using as my boss prefers us to use the online versions through our Dropbox to prevent conflicted copies. however not all features are available with online,
what do i do here?

Hi ambern1023!
Welcome to the ExcelDemy forum and thanks for sharing your problem with us. Creating a comprehensive Excel workbook for tracking vehicle maintenance, parts, and service history can be achieved using a structured approach. Here are some steps and suggestions to guide you through the process:

1. Workbook Structure:

• Maintenance Workbook:

Create separate sheets for each vehicle (Vehicle1, Vehicle2, etc.).
Include columns for date, type of maintenance, cost, odometer reading, and any other relevant information.
You can use a Microsoft Form to populate this sheet.

• Parts Purchased Workbook:

Create a sheet for each vehicle to track parts purchased (Vehicle1, Vehicle2, etc.).
Include columns for part number, cost, vendor, date of purchase, etc.

• Historical Info Workbook:

Create a sheet with a log of all vehicles and their respective maintenance history.
Use formulas or data linking to pull information from individual vehicle sheets.

• Regular Check Workbook:

Create a sheet to track regular services/checks with conditional formatting to highlight overdue or upcoming services.

2. Linking Workbooks:

To link data between workbooks, you can use formulas like VLOOKUP or INDEX-MATCH to fetch information from one workbook to another.
Ensure that you maintain a consistent structure and naming convention for vehicles across workbooks for efficient linking.

3. Handling Excel Online and Dropbox:

Excel Online may have limitations compared to the desktop version. If you encounter issues with links, consider using the desktop version for more advanced features.
If using Excel Online is essential, try simplifying the workbook structure, as complex links and formulas may not be fully supported.
Dropbox can be used for sharing and collaboration. Ensure that everyone is working on the latest version to avoid conflicted copies.

4. Additional Tips:

Regularly back up your Excel workbooks to prevent data loss.
Communicate with your team about using the desktop version when advanced features are necessary.

5. Consider Alternatives:

If Excel Online limitations persist, consider using other collaborative tools like Google Sheets or Microsoft Teams, which offer online collaboration without some of the Excel Online limitations.
Remember to customize the workbook based on your specific needs and adapt the structure as your tracking requirements evolve.

For more detailed information please go through the articles below:

Combine Multiple Excel Files into One Workbook with Separate Sheets (4 Ways)
How to Combine Data from Multiple Sheets in Excel (4 Ways)
How to Link Excel Workbooks (4 Effective Methods)
Link Excel Workbooks for Automatic Update (5 Methods)
Best Practices for Linking Excel Spreadsheets

Regards,
Md Nafis Soumik
ExcelDemy