Help with creating a 'cheat sheet' tool

Hi folks,
I am trying to create this workbook project. Having spent some time researching a solution to this, I 'enjoy' the power and use of spreadsheets but as a novice, when it comes to this level, I'm feeling slightly overwhelmed by all the potential functions and syntax etc.. I'm also approaching an imminent 'window of opportunity' to present this to our department's Mgmt team, so time isn't on my side..


I'm not expecting anyone to provide me with an entire solution (while that would be awesome) but would be grateful if I could be pointed in the right direction as to the best way to build this e.g. which functions and syntax examples I should be focusing on etc..

Some project background….
We have a large, very mixed, fleet of industrial equipment all requiring frequent planned maintenance. I am trying to create a sort of dedicated in-house, quick look up, 'cheat sheet' for the common maintenance items required for each piece of equipment.
The equipment's fleet numbers are the 'second language' of the company operation, so predominantly everything is geared around this number. Alternatively, the 'serial number prefix' (aka spec. code) of said equipment is also commonly referenced.
While each fleet# is unique, some have the same spec code, thereby using the same maintenance items. Furthermore, a spec code can occasionally use some of the same items as another spec code.
I have already built lists of all the common items used for each spec code, one per worksheet, each labelled by the spec code prefix.

My objective…
Ideally I would like to create a 'front end' summary sheet whereby, when a user keys in a fleet# *or* s/n prefix into dedicated cells in that sheet, it will auto populate that sheet with all the part numbers and descriptions, required only for that particular piece of equipment, 'pulled' from within the corresponding data associated to that equipment, within the s/n prefix sheets which I have already built. This sheet could then be printed to pdf to submit as an order for the items.


Naturally I could restructure the data in these sheets according to any instructions and guidance I am given to make this work. (e.g. move all data to one sheet and create named ranges?). I also want to protect all the 'back-end' data sheet(s) from being altered in any way.
Once created, I would save the workbook to our company department's OneDrive share, then share it from/via Excel Online (and possibly GSheets down the road).


I have attached a smaller sample of the sheet as it currently stands. Any guidance would be much appreciated as well as helping me to learn more using real world data. A feather in my employment cap wouldn't go amiss either
Thanks for taking the time to read!
Brian

Brian
I've done something similar to what you want using a combination of Excel Tables and Lookup functions. If you are interested in doing it this with I'll try and guide you through it. The only caveats are that I don't think the method I would use would migrate to GSheets and it would be based on Excel 2016 or later.
Paul

Paul,
Apologies for the late response and thanks for your offer to help. I'm definitely willing to have a go. Lack of GSheets compatibility isn't a big deal. At work we are using the latest Excel version within Office 365. But I only have time to tackle this in my spare time at home. Are you saying the methods/features you propose to use are only Excel 2016 onwards? I was hoping the 'tools' would be 'version agnostic'. Or is it possible to create it in Excel on-line whereby I could log into my corporate/employee account to build it on-line? Though I'm assuming the on-line version is limited in functionality.
Brian

Brian,
The Excel Table facility has been in Excel since 2007 but wasn't well documented until maybe 2010. The Query facilities were greatly enhanced in 2016 by the inclusion of Power Query as Data, Get & Transform. There are limitations in the on-line version but I've not used Excel on-line enough to discover any that stopped me doing what I needed to do.
I'll look at your model over the weekend, make a few modifications and post something ready for Monday, probably with a few questions for you.
Paul

Brian,
I've made a few changes to your model. I've made the FleetList, GEB, RJG and JJW in to Excel Tables. This was done simply by putting the cursor in the data and clicking Insert and then Table. Ive then created a list of unique Sn Prefixes and stored that on Sheet1 and referenced it by using a Data Validation list on the appropriate cell on the Entry Summary header sheet.
This could then be used as a parameter to populate a table, as yet undefined, for I don't understand the precise relationship between the Fleet List and the GEB, RJG JJW sheets or where the Item# fits in. Please could you explain more of your thinking.

Regards
Paul