Graphical search for excel

[Aquilla]

Ok, so first off this is my first post here, so hi everyone

And now for why I joined, I'm currently trying to create a parts database for customers, Ive got about 1500 lines of data and need a graphical search function so the customer can enter a few criteria, eg description, machine type etc and it will return all matches, the data is currently in excel. I know I've not explained particularly well but please ask and I'll try to explain better. Any help at all would be greatly appreciated.

[CoffeeFiend]

create a parts database

the data is currently in excel

There's your problem. You want a database, but you use a spreadsheet instead. Completely wrong tool for the job. There are way to filter data tables manually in Excel but that isn't quite what you're asking for, and it's still the wrong tool for the job regardless. I'm not a big fan of MS Access but it's a far better tool for this (it's also part of MS Office)

[jaclaz]

I beg to differ

For a trivial amount of data (like 1500 lines are) Excel is a rather handy solution, and it offers "by default" nice filtering criteria in a substantially more "user friendly" or "common use" way.

Of course with Access one can create a "better protected from casual user mistakes" app, but is it worth the effort if you are not familiar with the app? I personally doubt it.

@Aquilla

Which Excel version (or Office version) are you using?

FIrst basic tutorial I found (Excel 2003):

http://spreadsheets.about.com/od/datamanagementinexcel/ss/excel_database.htm

jaclaz

Edited March 18, 2011 by jaclaz

[Aquilla]

thanks alot for the quick reply guys,

CoffeeFiend, Ive used excel for two reasons really, the first being im doing this on a works laptop, which does not come with access installed standard, I can request it but it takes a few days to actually get it, after that other intercompany users will need to use this from time to time, if they have to go to the trouble of requesting software to do so it just wont be used, the second reason being that there will be new entries to go on every few days or so and for this ive created a VBA based user form for the previously mentioned intercompany users. the amount of data will most likely never exceed 4000 lines.

jaclaz,

that probably would have been a good thing to mention to begin with wouldnt it

Im using 2007

[jaclaz]

Im using 2007

I am not very familiar with that version, but the "standard" filtering has changed a bit.

http://www.contextures.com/xlautofilter01.html

If you are familiar with VBA, and more generally with scripting, you may find this useful (which BTW is the living proof from the actual mouth of the wolf that BOTH CoffeeFiend and myself are actually right ):

http://support.microsoft.com/kb/257819/en-us

INTRODUCTION

The rows and columns of a Microsoft Excel spreadsheet closely resemble the rows and columns of a database table. As long as users keep in mind that Microsoft Excel is not a relational database management system, and recognize the limitations that this fact imposes, it often makes sense to take advantage of Excel and its tools to store and analyze data.

Also:

http://vbadud.blogspot.com/2008/05/using-excel-as-database.html

http://itknowledgeexchange.techtarget.com/beyond-excel/

A couple "old school" thingies (ITALIAN, sorry ) but the code should be easily understandable and google translate often does miracles .

http://www.ennius.altervista.org/vba/vba78.php

http://www.ennius.altervista.org/vba/vba79.php

But there quite a bunch of methods/ideas/possibilities, another one:

http://xldennis.wordpress.com/2010/09/25/create-sql-server-compact-edition-database-with-vba/

It is not clear to me (yet ) if you are actually plan to write a small VBA or you are going to just use "plainly" Excel "normal" features.

jaclaz

[Aquilla]

Thank you jaclaz, the above looks very helpful and I'll take a fuller look when I'm back in the office.

I do have a further question, if I may, a few people, (on here and colleagues) have mentioned that I would be able to do this more elegantly in access, my question is for a computer literate access newb how hard would it be to transfer the data to access and then create the required search function?

[CoffeeFiend]

Assuming the "flat" design you have (i.e. using a single database table) it would be quite simple. Definitely within the reach of a beginner who's open minded, willing to learn and try new things. Access is meant to be used by mere mortals (regular old office workers without advanced training of some sort). If you keep it simple enough, you could make something half decent in a few minutes. There's even wizards to help you do most of the job (it'll generate the entire form for you, leaving mostly minor tweaking or cosmetic changes to do)

Like I was saying, filtering in Excel (as a substitute to a DB) isn't so great, and a LOT of users won't actually see (and often don't understand) the tiny flags that appear in the column headers when you're filtering. Then they just think it's broke somehow (or that the part doesn't exist/isn't available, etc). It's not so great for searching either (just filtering).

VBA can be a pain with all the security settings these days. It's often disabled for security reasons (thanks to old macro viruses), it requires confirmations to somewhat scary warnings that often scare users (so they leave it disabled and then it doesn't work). VBA can be powerful for some things, but it's a lot more complicated than just using access here, and it's not exactly a great development environment either (starting with the language itself or the tools).

Access would let users type in text to search for and what not on a simple and user-friendly form. No security warnings or anything weird/unusual. No programming required. That's why I suggested it before. Access is meant precisely for this kind of usage. Considering all options, excel + filtering and/or VBA is about the dead last option I'd pick myself.

Personally, I make a client app most of the time (C#/.NET FW 4/EF4/LINQ/SQL Server/Reporting Services) or a sometimes an ASP.NET MVC web app (same stack mainly), but that's quite a bit more complicated than just using Access.

[jaclaz]

The general idea is that there are two basic kinds of Databases:

• "flat"
  
• "relational"
  

As seen before a spreadsheet can be used for a "flat" one with no problem, and using VBA it is actually possible to link a spreadsheet to a "data source".

The spreadsheet is more a WYSIWYG kind of approach.

Database have a functional difference, whihc is that you have DATA (that you fdon't actually "see") which you interrogate through a Query and that generates a Report.

An extremely simple form of "flat" database with Query (actually Filters) and Reports is the integration between Word and Excel when you use the "mail merge" feature:

http://office.microsoft.com/en-us/word-help/create-and-print-mailing-labels-for-an-address-list-in-excel-HP010243267.aspx

If you go through the above, you should get the general idea, and then making it through Access should be easy enough, once you also go through this:

http://office.microsoft.com/en-us/access-help/create-mailing-labels-in-access-HA010007352.aspx?CTT=5&origin=HP010243267

jaclaz