Searchable Excel Workbook Database

Hello all,

I've always received a lot of help from this forum, despite my ignorance. I'm hoping for another lightning strike, because I'm desperately in need of a solution.

I have an old excel database that is used for tracking incoming and out-going customer tools. It's huge, clunky, and badly formatted.

What I would ultimately love to turn this database into is the following:

An Excel workbook with a template on the first page, that saves data entered into it on a new sheet. So every shipment of tools would have a new sheet created, based off of the data entered into the template on the front page. This should be easily searchable using the "search workbook" function (or even better, a search function built into the template or splash / welcome page), and therefore we could update what I call the database (I'm sure it's not really a database, but I don't know what else to call it) by going to the tool shipment's sheet and changing the information there.

We often get 100-150 tools per shipment that need tracking. Our inventory software (Fishbowl Inventory) doesn't provide any way of tracking the volume of tools we get in, or of sorting what has been finished and what still needs work. Really, the data required is very simple, and as I said, we're making do with a monsterous spreadsheet right now. However, if I could clean it up with a macro or database that seperates the shipments out by placing each shipment on a seperate sheet, and updating a few key fields, our work load would be 1000x easier.

I am able to create the template, but I do not have the knowledge or skills to create a code or macro to have the template export the information entered into a new sheet. If anyone would be willing to help me, I would be more than happy to work up a basic layout.

Thanks for reading.

- Edit

I forgot one other thing. The searchable part in the headline refers to the following:

Once a tool is completed, I'd like to have a checkbox or something similar that indicates that. If it's possible, it would be great if we could search for only open tools, or only tools that have been completed, by customer name.

So say my customer calls and asks how many open tools we have for him, I can search for open tools and the customer name, and be rewarded with a list of sheets that I need to print out.

I guess I should list the search terms that I would like to use.

Customer Name
Open or Closed
Date Range
Refurbish or Clean and Test
ID Number
Manufacturer
Shipping Method
Tool Type

Just to be clear, I really have no idea if this is a colossal amount of work, or relatively simple. So if I'm asking for something ridiculously complicated, then please let me know and I will try to find another solution. It's already been suggested to me that we use another inventory software, but using two different inventory systems would really screw up our warehouse and receiving people, and we're pretty entrenched in the one we have. Also, it works great for every application save for this one.

Hi,

The best thing to do is put together a template, then upload it so that we can comment when we see your request in context.

Rgds

Ok, I uploaded the template. If you need any more information or have any questions, I'll be checking this periodically throughout the day.

Thanks for looking!

Hi,

I'm attaching an idea that you can maybe expand on.

First things first.
1. Try to avoid merging cells. In my opinion they just get in the way. You can achieve the same thing with formatting alignment across cells
2. When capturing dates work with date numbers. Your drop down for the month shows month names. I've changed it to reflect numbers. There are ways of seeing names in the drop down but capturing the number but in this quick example I've not bothered with that.
3. Since you're wanting a database, you need to have just one data entry form. There's no point in stacking the same form one under the other as you have done,
4. Again, since you're after a database, each record you capture should go on the same sheet, not separate sheets for each customer.
5. The 'Add Record' button runs a simple macro which will add the record details to the database. The Tool No field automatically increments by 1 each time you add a record.

As I say this is the beginning of an idea, and in the real world you'll need to enhance it so that you can amend, or delete records already added. And of course you'll want to create a filtering macro which will extract records from the database to an output sheet.

I've not tidied it up which you'd obviously want to do, but these are details that can be left too later.

Hope this gives you some ideas.

Regards

Wow, this is really cool! Thanks a ton for doing this!

Is it possible to make three changes?

Could there be a button to go along with the submit button that creates a new sheet in the workbook, and also routes the information sent by the submit button to the newly created sheet (leaving the previous sheet to be updated manually as the tool works its way through the sytem)? Such functionality would create exactly the database system that we need. Basically it would be a "New Shipment" button, that could be used to divide up different shipments in the system, allowing us to print logs for workers and customers.

Second, is there a way to add a button that clears the form, or make the submit button automatically clear the form when it's pressed?

Lastly, can the sheet that the data is exported to from the submit button be formatted at all? It would be extremely useful if the drop-down menus went along with the data, so that it could be updated as the tool progresses through the system.

Oh, and one other small change... Is it possible to have the preview date from your example put into a table, so that it's easier to review?

I've uploaded a new template with this post, that hopefully works better for what I'm asking for. I took your advice, so I'm hoping it will be easier to work with. Although I'm not sure how to accomplish some of the formatting that I needed without using merge, so there are still some merged cells.

Once again, thanks for your help! If this works, it will be a life saver.

OBA, you might try posting in the commercial services exchange forum. I'd be surprised if someone doesn't offer to help -- there are certainly people here that can do what you want done.

OBA, sounds like you have a hot candidate.

I would email it back to you directly (saves time, rather than going through the forumn upload & download).

And preferable; to be clear, the forum is not party to any arrangement between you two.

Good luck, OBA, hope you get what you need.

Glad it worked out for you.