Hello all,
I have a challenge that I would like to solve with Excel and the Windows Search.
Objective:
Certain Excel documents (e.g. quotation calculations) should be made easier to find using Windows Search for file contents. It should be possible to search by customer, machine, material numbers, etc.
Problem:
The Windows Search returns a lot of results. Too many! Customers, machines and material numbers occur in many different documents and folders, sometimes even in every template (e.g. as a predefined list for a dropdown menu, similar to the yellow fields, see below). A targeted search is thus hardly possible.
Idea - previous approach:
If the desired search terms are combined with a certain prefix (here SW#), an exclusive string is created. For example, the material "Steel" becomes the string SW#Steel.
Now all documents with the string SW#Steel can be found via the Windows Search easily. All files, file names and folders containing the term "steel" will be excluded from the search results.
With a combination of different keywords the search can be refined. For example, all documents of steel articles created in 2023 for the automotive customer OESW can be found. Just search: SW#Steel SW#2023 SW#OESW SW#Automotive
This works wonderfully and would be a powerful search tool in theory!
Of course, no one wants to manually keyword their documents in this way. An automatism would be great, which generates the searchable string from the user input and the prefix. Theoretically this would be possible with "=TEXTJOIN (...)".
Unfortunately, there is a catch: there must be no formula in the green cells, otherwise the Windows Search will remain blind!
Wish:
The keywords in the green cells shall be generated automatically from the user input in column C. There must be no formula in the green cells, the cells would have to be filled with the corresponding text in some other way (if necessary also via VBA). Ideally, this would all happen fully automatically in the background, without the user having to intervene again.
Does anyone have an idea or a guideline how a solution could look like? How can I make a combination of prefix and keyword readable for Windows Search? Maybe there is also a completely different approach to solve this, e.g. via the meta tags of the file or something similar? In any case, the goal defined above seems reasonable and tangible to me. In every second online store a search can be limited via #keywords or similar. This should be possible with standard Windows and Office functions.
I am glad about every hint. Thank you for your support :)