29
Jul/10

Spreadsheet or Database? Excel or Access

by Hazel under spreadsheet or database

Do I need a Spreadsheet or a Database?  Excel ® or Access ®?

Do you have a spreadsheet which takes an age to open and close?  Are you struggling with a huge number of tabs at the bottom of your spreadsheet?  Do you have a network of spreadsheets linked to each other?  You don’t necessarily need to, because this situation can be easily be remedied by converting a spreadsheet, or a series of spreadsheets, into an Access® database. ®

What is a database as opposed to a spreadsheet?

Databases store their data in tables.  These tables are similar to single two dimensional spreadsheets which are “related” or linked to each other within the database. Move into the 3 dimensional Access ® world and change the way you store your data.

How do I decide Spreadsheet or Database?

This is probably the most difficult question to answer. Basically if your data is “flat”, “two dimensional” and consists of one table (spreadsheet tab), without any external links to other spreadsheets – it should be a spreadsheet.  If, however, you have multiple tabs all referencing each other or linked to external files then most probably you should be thinking “Access® Database”.

The beauty of a database is that it can be designed with a user friendly “front-end” (a menu).  Once the database is in place the user does not need to understand Access ®, or get involved in any of the technology directly, everything can be carried out from simple button menu options.

Conversion

Most people understand Excel® at least at a basic level and are reluctant to learn a new program.  I have to admit Access ® is not intuitive, but it is powerful and will deliver a great deal more than Excel® has to offer in terms of data storage and retrieval.

Access® files are compact and open and save very quickly.

Offer

If you have an unwieldy spreadsheet and you just don’t know whether this would be better handled in Access ® then contact us at Spreadsheet Now and we’ll take a look FOR FREE and let you know your best course of action.

We can not only advise you on which option is best for you but we can actually carry out the conversion for you, for less than you thought!  Try out the link opposite to Spreadsheet Now, fill out an enquiry form and we will do our level best to help.

How do I manage Access ® myself?

If you want to accept the Access® challenge yourself, then you will need to search the web and the help files within the program and build up your knowledge from scratch.  It is a complex program, but it is well worth conquering if you have the time and inclination.

Start by using the standard Access® options on the menus and when you are familiar with them you can move on to Access® VBA (Visual Basic for Applications).  VBA code will allow you to expand the functionality almost beyond your dreams.  See my posts on VBA.

Remember my favorite 4 rules:

  • Take it one step at a time
  • Don’t give up
  • Believe – it is possible
  • Persevere

Hazel Lowe

, , , , , 57 Comments more...

9
Jun/10

VBA – The Sequel – Let’s build a Database

by Hazel under VBA - The Sequel

Introduction

I have received so many positive comments since the publication of my first post on this subject I felt it might be helpful if I wrote a continuation on VBA for beginners with more detail of the exact code required to run a simple process using VBA in Access 2007, just taking you a few steps further forward.

The background

My first blog on the subject was Access VBA Code – Beware it can seriously damage your sanity!

We took a simple example and provided code to

  • Get a contact name
  • Validated the input received
  • Saved the input in a table

If you have not read that blog I suggest you read it first in order to give this sequel context.  However I did not provide the full code in Part one, but below I do supply the full code and we also get the code to do something.

The Database

Here is my example Database (Access 2007).

As you can see it has 2 tables: “CONTACTS” and “InputTable”

1 query: “MYQUERY”

1 Form:  “MENU” this is the form which houses a single button to run the code

2 Macros:  “RunModule” to run the Button on the Menu and “ClearInputTable” to clear the InputTable after use ready for the next time the button is pressed.

1 Module:  “Module 1” This is the VBA Module which holds the code for this procedure.

The next tentative step

Here is the exact code which will take us as far as we got before:

Option Compare Database

Option Explicit

Public Function RunCode()

Dim db As DAO.Database

Dim ContactName As String

Dim MyInputTable As Recordset

Dim OK As Boolean

OK = True

Set MyInputTable = CurrentDb.OpenRecordset(“InputTable”)

‘offer for user input and validation

Do

ContactName$ = InputBox$(“Enter Contact Name”, “Category”, “<enter either Jones or Lowe>”)

If ContactName$ = ” ” Then OK = False

If DCount(“ContactName”, “Contacts”, “[ContactName] = ‘” & ContactName$ & “‘”) > 0 Then Exit Do

If DCount(“ContactName”, “Contacts”, “[ContactName] = ‘” & ContactName$ & “‘”) < 0 Then OK = False

MsgBox “User Name Not Found!”

Loop Until OK = True

‘Store the ContactName in a table

MyInputTable.AddNew

MyInputTable(“InputText”) = ContactName$

MyInputTable.Update

The Query

Ok so far.  Now we need to use the saved data to run our Query.

First you will need to create the appropriate query.

Use the wizard or create in design mode and relate the Table “CONTACTS”.  Pull all the relevant columns required into the query and save this query as “MYQUERY” or whatever you want to call it.

Below is the query in design mode:

As you can see this is a stand query running off one Table “CONTACTS”

The magic words

OK now we need to ensure that the VBA module correctly filters this query when it is run.

There are a number of really confusing ways to do this, but the following is the simplest way I have found and it really does work.

In the query “MYQUERY” in the “Contact Name” column in the Criteria box type the following equation:

DLookUp(“InputText”,”InputTable”)

The Result

When the query is run within the VBA Function it will run the query using the name which was stored in the “InputTable”.

Completing the Code

Now we have to complete our code:

‘we open the Query.

DoCmd.OpenQuery (“MYQUERY”)

‘we clear the Input Table

DoCmd.RunMacro “ClearInputTable”

End Function

The Full Code

And here is the full VBA Code

Option Compare Database

Option Explicit

Public Function RunCode()

Dim db As DAO.Database

Dim ContactName As String

Dim MyInputTable As Recordset

Dim OK As Boolean

OK = True

Set MyInputTable = CurrentDb.OpenRecordset(“InputTable”)

‘offer for user input and validation

Do

ContactName$ = InputBox$(“Enter Contact Name”, “Category”, “<enter either Jones or Lowe>”)

If ContactName$ = ” ” Then OK = False

If DCount(“ContactName”, “Contacts”, “[ContactName] = ‘” & ContactName$ & “‘”) > 0 Then Exit Do

If DCount(“ContactName”, “Contacts”, “[ContactName] = ‘” & ContactName$ & “‘”) < 0 Then OK = False

MsgBox “User Name Not Found!”

Loop Until OK = True

‘Store the ContactName in a table

MyInputTable.AddNew

MyInputTable(“InputText”) = ContactName$

MyInputTable.Update

‘we open the Query.

DoCmd.OpenQuery (“MYQUERY”)

‘we clear the Input Table

DoCmd.RunMacro “ClearInputTable”

End Function

Fairly unimpressed?

I expect you thought you’d get a great deal more happening when you bear in mind the amount of code required to accomplish this simple routine.  This is just the “tip of the iceberg” it’s a simple, silly example, however, it does illustrate how VBA can be used very effectively to run routines which involve Tables, Queries, Forms, Buttons, Macros and Codes.  You can now build on this very basic model and create really fantastic code of your own.

Last Notes

I hope you have found this information useful.  Please do leave comments as this is the only way we can assess whether further posts of this type are needed out there.  Good Luck.

June 2010

, , , , , , , , , , , 8 Comments more...

3
Jun/10

How to create a free video for your website

by Hazel under Video Creation

Is this possible?  Yes, there are many free applications out there to do this.  You may need a little guidance as to how to achieve this.

What do I do now?

Firstly you must decide where you wish your video to reside.  I would recommend you use YouTube. It is the preferred place and it also has a number of advantages over other sites.

There are significant benefits from placing your videos on YouTube®, hopefully they will attract those people who are just searching videos. Loads of people spend a significant number of hours daily browsing through the latest and most interesting films available.  This traffic can then be directed to your website which in turn should increase your web visitors.  Also YouTube® allows you to share your activity with Facebook® and Twitter®, so all your friends and contacts will be notified when you upload new items.

There are always limits – what are they?

The limits are:

  • up to 10 minutes only
  • up to 2 GB size
  • MPEG4, 3GPP, MOV, AVI , MPEGPS,WMV and FLV
  • A word of warning regarding content.  Do not upload any TV shows, music videos, music concerts, or commercials without permission unless they consist entirely of content you created yourself. By uploading, you are representing that the video does not violate YouTube’s terms of use and that you own all copyrights in the video or have authorization to upload it.
  • Firstly open a YouTube® account – it’s FREE.
  • Familiarise yourself with how YouTube works
  • Take a look at the Upload suggestions for assistance on http://www.google.com/support/youtube/bin/static.py?p=my_videos_upload&page=start.cs&hl=en_US

Look at YouTube

This will assist you giving the recommended size, resolution etc.  This will enable you to create a video that you know will be compatible with YouTube® when you come to upload it.

Choices

Now you must decide how you are to record your video.  Do you want sound and visuals, will these be produced using a camera, an existing PowerPoint® presentation or whatever.

Recording PC Activity with Audio

If you wish to record activity on your PC whilst recording audio then I would recommend the Webex® Free Recorder.

There are 3 components:

  • The Recorder
  • The Player
  • The Editor

Try http://webex-recording-editor.software.informer.com/2.4/

Settings – how?

This little application is quite easy to use, though you may need to tweak the audio quality.

Initially I used the default settings, but the audio was not very good and I wanted to hide the control panel while I was recording so I used the following settings. Under Settings:

Webex® Recorder Settings          and audio settings

What do I need?

You will need headphones attached to your microphone jack – you can then set the recorder going and open whatever you are wishing to display on your video e.g. Presentation, application etc.  Before you begin the recording I would suggest that you prepare a script, if you are familiar with working from bullet notes then that is all you will need to prepare.  I prefer however to work of a complete verbatim script, this ensures that I do not miss out on any of the important points you wished to make.

When you have finished then you stop the recorder and play the result using the player.

Show me

Here is an example of a video recording made using this method:

http://www.youtube.com/watch?v=ej9TmSBNZsE

This was created using Webex® Recorder and then uploading to YouTube®.  On the YouTube® site they provide the embed code you need to paste into your web page.

Presentations to Video

If you wish to show a PowerPoint presentation in video format then you will need another free application Author Point Lite® available from http://www.authorgen.com/authorpoint-lite-free/powerpoint-to-flash-converter.aspx

This is very easy to use and allows you to import your PowerPoint® presentation into the program, and it then converts it to a flash file which is uploaded to their server.  They then send you an email when the conversion is finished.  You can then share the presentation through it’s unique link, or embed it as an object in a blog or website.

Show Me

Here is a recording of a video produced from a presentation (with no audio)and converted to Flash using Author Point Lite®. http://www.erlsolutions.com/video.htmlI f your presentation has audio, this will be converted into the final file as well.

I hope this short blog has given you an insight in how to produce reasonable video without incurring any cost.  It’s amazing what you can do if you search around a bit for an answer and devote a little time to your ambitions.  Good luck.

June 2010

, , , , , 557 Comments more...

1
Jun/10

Websites – the first simple steps

by Hazel under Website Advice

The beginning

Do you keep stumbling across people who tell you “I’ve just created a website”.  Do you think, “If they can do it, why can’t I?” – Absolutely no reason at all,

While in principal this may appear to be a simple matter, there are a good many obvious pitfalls, and some not so obvious!

Firstly you will need a means of editing the website, for this I use Dreamweaver.  When you first open this application a frightening array of menu options are displayed, but just treat this like a washing machine, that is there are many programs available but you will probably only use one or two.

Or perhaps you have found an online site which will allow you to create your website.  Whatever your choice you will start with a blank page.

It’s a blank

Now you need to place something in your website, images and content.

  • Plan on paper
  • Collect suitable images and make sure you are not breaking any copyright conditions
  • Decide on layout i.e. where will you place the images.
  • Create any videos (this is a complete subject in itself, blog page pending on this subject).  It is simplest to use YouTube to host the video and code it into your page using the embed code on the YouTube site.  But watch out their code is written in html.  If you are using xhtml you will need to amend the code.
  • Decide how you will navigate through your site, via buttons or hyperlinks
  • Write a few snappy phrases outlining your products and services
  • Remember key words are KEY.
  • If you are selling from your website you may need to install PayPal buy buttons, these are available from Paypal.co.uk.  But watch out their code is written in html, if you are using xhtml you will need to amend the code.

Building the site

So you have all your content now you need to place these on the appropriate pages.  Ensure that you link pages correctly to avoid 404 error pages popping up.  Nothing is more off putting than finding broken links on a website.  Visitors will swiftly move away if they find errors on the site.

To create your own “not found” page is probably a good thing, directing any lost traffic directly back to your home page or wherever you want. This is a much friendlier way of dealing with 404 errors. Try this link for an example http://www.spreadsheetnow.com/notfound.html.

Problems you never thought about

The major hindrance seems to be that there is no joined up thinking as regards browsers, for example what works on Firefox and Chrome may well not work on Internet Explorer and visa versa.  There are ways of eliminating these differences using xhtml code, but this can be a very lengthy and irritating process.  So check your web pages in all the browsers to ensure that your visitors are seeing what you want them to see.

Remember

Websites are not static pages of information, they need to be changed and updated.  Keep implementing new ideas to attract AND KEEP your public visiting, and most importantly staying.  The search robots will respond better to sites which are constantly updating their pages, and thus you will fair better in the rankings than if you leave your pages unchanged month after month.

HTML or XHTML

Whether you write in code, or plain English using something like Dreamweaver, when your pages are ready and have been published onto the web, I would advise you to use a “Validation” application to check that your code is valid from top to bottom.  I use http://validator.w3.org/ give it a try this will give you peace of mind that your code is squeaky clean.

Help on the Internet

There is loads of help available on the internet, this blog for example.  Much of this so called help, however, can serve to confuse rather than assist, and the searching can be very time consuming.  Rest assured there are good sites out there even if finding them can be quite tiresome.

June 2010

, , , , , 405 Comments more...

14
May/10

Access VBA Code – Beware it can seriously damage your sanity!

by Hazel under VBA - The Beginning

The Rules

  • If you are reasonably familiar with Access, and decide to enhance your database beyond the standard access functionality, then you will probably find yourself knee deep in VBA code, and gasping for breath.  You know you can do what you want with the code, but you’re not sure where to start.  May I suggest?
  • Take it one step at a time
  • Don’t give up
  • Believe – it is possible.
  • Most importantly persevere.

Resources

So you know what you want to do, and now you want to know where to begin.  You can get assistance using the following:

  • Books – There are some very good, but expensive, books on the subject,
  • The Internet – At first glance there appears to be heaps of assistance available on the internet.  This can be a mixed blessing, however, so be prepared to trawl through hundreds of Google results to find each small piece of your VBA jigsaw.
  • Databases – Examine an existing access database; the coding may give you a clue.
  • Forums – Internet VBA forums can be helpful, but often offer conflicting advice.
  • Access and VBA Help – OK if you know what question to ask.

Some of the advice on the web is to say the least misleading, and at best confusing.  Much of the advice available seems to serve to enhance the writer’s reputation as an expert rather than being of any direct help. The experts have a habit of referring indirectly to just what you are looking for, but not expanding on how to do it, while expanding in detail on all the aspects you did not want.  Don’t get me wrong, I have found some very good sites with very sound advice, but it requires hard slog to sort the sheep out from the goats.

Access and VBA “Help” proves to be sparse where you need a straightforward answer to a straightforward question, and far too detailed to be of use once you figure out how to ask the question.

Books and reference guides are useful in outline, but again you may need to trawl through several to find the answer to your simple question.

An Example

Let’s take the following as an example.  You have a table called “Contacts” and you need to get a user to input a contact name so that you can use this contact name further on in your code.  Ok you quickly gather you will need to put in something like the following:

‘Get the contact name from the user

Dim Variable As String

Input$ = InputBox$(“Enter Contact”, “Select Contact”,”<Enter Contact Here>”)

Ok so you have provided a box called “Select Contact” which allows a user to input a contact name and store this name in a variable called “Input$”.

It may be appropriate to warn you at this point that this is just the start of something BIG.  You soon realise that you will need to validate the user input, to ensure that whatever they put in the box, or do not put in the box gets the required result.  This will require further VBA code along the following lines:

‘Validate the user input

Dim OK As Boolean

OK = True

Do

If Not IsNull(Input$) Then OK = False

If DCount(“Contact”, “ContactsTable”, “[Contact] = ‘” & Input$ & “‘”) > 0 Then Exit Do

If DCount(“Contact”, “ContactsTable”, “[Contact] = ‘” & Input$ & “‘”) < 0 Then OK = False

MsgBox “Contact Name Not Found!”

Loop Until OK = True

Ok so now we have our “Input” but if we need to use this later in our code we will need to save it somewhere.  Probably the easiest and least confusing way is to save it in a single record table, it can then be retrieved for whatever means are required later.    First create a table with one column called “InputText”.  Then enter the following, or something similar, in your VBA code

‘Store the input in a table

Dim MyInputTable As Recordset

MyInputTable.AddNew

MyInputTable(“InputText”) = Input$

MyInputTable.Update

I think this small example illustrates how soon one can drown in code when just carrying out the simplest of processes.  All we have managed to achieve with the three sections of code above is:

  • Got a Contact Name
  • Made sure the input was valid,
  • Saved the Input

Reaching a dead end

Be prepared to follow advice even when you believe this may not be the best solution.  You will learn something useful along the way, even if you later find a better way to do it.  Sometimes the advice you receive will involve you in days of frantic activity, but in the end it may well take you right up a blind alley.  So get used to hitting those brick walls, it hurts a little less each time.

Encouragement

Have I put you off?  Don’t be discouraged, just keep to the rules:

  • Take it one step at a time
  • Don’t give up
  • Believe – it is possible.
  • Persevere

VBA code is incredibly versatile and powerful, the only limitation is your imagination and the time to collate the required pieces of the jigsaw.  Good luck, and remember “If at first you don’t succeed then try, try, try again”, it is worth it in the end.

Hazel Lowe May 2010.

, , , , , , , , 356 Comments more...

6
May/10

Clouds on the Internet?

by Eric under General IT Advice

Clouds on the Internet?

A new way of using PCs is coming which could help your business
Love them or loathe them, most people today have to use computers for some reason or other. It may be just to book flights or send and receive emails; or it may be for business and all financial matters. Most business users experience problems with storage, upgrades or just getting at your information which leads to frustration and reduces productivity.
There is a new approach that is now beginning to emerge that changes the way we use computers altogether. It is called “cloud computing” – and this article attempts to offer an introduction to the issue without “clouding the issue” in confusing computer terminology.
Some Basics
Stepping back and taking a basic look at our computers, they all share some common component parts. They have:

  • a store of information – usually called a hard disc
  • some brains – usually called a processor with some memory to speed things up
  • some cement that binds this all together – called an operating system like Vista
  • and something that uses the information in a way you want – usually called an application e.g. Microsoft Word

What’s the problem?

The problem is, we just have more and more information to store – photos, documents, videos, emails and so on – which means we have to look after this information carefully and from time to time buy a bigger store. I am old enough to remember when 4.8 Megabytes was considered huge – now we think in terms of thousands and even millions of Megabytes. Added to this, the cement keeps changing and being upgraded and new, better uses for our computers force us to invest in them time and again.

How Does Cloud Computing Help?

The idea of “Cloud Computing” is that we should be able to just plug into the Cloud and use whatever computing resources we need as and when we need them. An analogy is moving from our own diesel generators for the supply of electricity in our houses to having electricity supplied from the grid as and when we need it. Most people today “plug into the mains” as soon as they can. How then does this work for simple laptops and small PCs?
The answer lies in the connection to the internet. Providing we have a reliable connection to the internet, there are services available now that allow us to store our information well away from our own computers. The Internet is a Cloud that offers a range of services if you know what to plug in to!

Some Examples

A simple example of this is Googlemail or Gmail. Many of you will use this free service already for your emails. If you have a Gmail address, you simple plug into the internet (not necessarily on your own PC) and access your emails which are stored securely by Google many kilometres away. Other providers like Yahoo and MSN are also offering free services and new Cloud services are becoming available all the time.
The point is that you don’t “own” the email service – you just use it when you need to. You don’t know where the emails are actually stored or what version of the email application is being used – it just works and delivers exactly what you need when you need it. And most of these email services within the cloud are FREE!

So What?!

So What?
How does this help me with my computer? Here are a few potential benefits:

  • IF your information is stored away from your computer you no longer have to buy new hard discs AND you don’t have to worry about backing up or making copies. All that is taken care of by the “cloud”.
  • If your information is available only to you but through anyone’s computer you don’t have to carry around your own computer.
  • At the moment many cloud services and upgrades are free. You no longer have to upgrade your applications as you are using applications from the cloud.
  • Hard discs break. So do processors. Computers become “corrupted” and need rebuilding. As long as you can get on the internet such disasters are minimised.

Drawbacks

As with every change, there are new limitations that emerge from this idea of using the internet as a cloud with resources available as needed.

  • The first and most obvious, is the absolute need to access the internet. Depending on where you are, the availability and the quality of service can vary widely. Try travelling in parts of Africa to understand this basic problem!
  • Then there is the fear of not being able to touch and feel the computer that holds MY information. This concern is, I would suggest, more in the mind than in fact. How secure is your computer today? How many computers are stolen every day? How well do you really protect your information? In contrast, Google, Yahoo and all the organisations involved in this cloud computing MUST provide the highest level of security both in terms of access and of remote sites continually copying information. Put another way, most small computer users have “single points of failure” whereas cloud suppliers cannot afford to. Many major corporations are moving to cloud computing right now.
  • Also, not every use of information is available on the internet. Specialist needs which might involve very specific software and large lumps of information do not currently lend themselves to the cloud computing idea.
  • Perhaps the most significant practical drawback is the potential loss of performance if the internet link is slow or there is a major problem on the worldwide internet. This can be very frustrating – but then computer users are familiar with frustration already!

Is this just a dream?

Interestingly, as I am writing this article, a Press Release has been put out by Telefonica headed
“Telefonica Entering Into Cloud Storage in Spain” January 15th 2010. One paragraph reads as follows:
“Terabox, based on Digi-Data’s V4 online storage technology, is available to new and existing Telefonica ADSL customers in Spain. The service enables customers to store, share, back up, organize and remotely access valuable digital files, including documents and multimedia, from anywhere there is an Internet connection.”
It is clear Telefonica are moving in the direction of cloud computing.
Have a look at Google (I am no great Google addict – they just have some good stuff in this area) and find the Documents they offer today. Dig in and you will see you can use their Documents, Spreadsheets and Presentations to create your own, or send your own Word or Excel or PowerPoint files to Google and have them stored by Google and available to you when you want.
I use a software package to do my own local backups and they offer 1 Gigabyte (a lot) of free storage on their cloud computing service.
There are numerous photo storage services – although you have to be careful about the resolution (detail) of the pictures they will store – but all of which take the strain of storing your photo collections.
If you need to share…
Some of the cloud computing services allow others to see your files IF you allow access. If you are discussing a particular document or want to share a presentation this can be a real boon. Again I use this function and was able to make a presentation with a colleague in Japan using a combination of Skype (free internet phone service) and Google storage.

Conclusion

When it comes to computing and accurately predicting where the future lies – many have tried and most have failed. For every 10 good ideas maybe one becomes the accepted “way”. The aim of this article is simply to alert the reader to the growing move to cloud computing, so when the term is used at least it means something. It does seem that this is gaining real traction and it does fix real problems for the “small user”. It certainly offers an alternative today to the never ending cycle of upgrading and investing heavily in our own computers. Whether it is for you is, of course, entirely up to you. At least you now know there is another option.

Footnote

Thanks to ERL Solutions for this interesting article.

Please visit  ERL Solutions using the link opposite.

or one of their NOW! IT divisions

, , , , , , , , , 39 Comments more...

6
May/10

How to Create A Spreadsheet

by Hazel under Spreadsheet Help

A little background

What is a spreadsheet?

A spreadsheet is a document that stores data in a grid of horizontal rows and vertical columns. The rows are usually labeled using numbers (1, 2, 3, etc.),  and the columns are labeled with letters (A, B, C, etc).  Individual row/column locations, e.g. B2 or H8 C3, are called cells. Each of the cells on a page an store unique data.  By inputting data into a spreadsheet, information can be stored in an more organized way than using text.  The way the data is contained in this row/column grid allows it to be analyzed using formulas and calculations.
The most commonly used spreadsheet application is Microsoft Excel®, but several other spreadsheet programs are available including IBM Lotus 1-2-3 for Windows and AppleWorks and Numbers for Mac OS X.

Why use a spreadsheet?

Spreadsheets address a wide range of business needs. Broad areas of use include cash flow and bank reconciliation; invoicing and accounting systems; statistical analysis and reporting. There are many vertical industry specific uses for spreadsheets.

However, there are circumstances where data might be better stored within a database such as Microsoft Access®. Making the right choice is critical if you want to access and update your information accurately. Spreadsheets are commonly used to manage lists of data such as telephone numbers and personnel data. In comparison, database applications store data in tables that look much the same as spreadsheets but are able to carry out complex querying using relationships between multiple tables.

So, while both programs manage data extremely well, each one has clear advantages depending on the type of data you are managing and what you want to do with it.

Simply put, if you can store your data in a single page or sheet, this is called flat or non-relational data, this type of data would be suited to a spreadsheet application e.g. Excel®. However, if your data needs to be stored in more than one table, this is called relational data, then you will need a relational database application, e.g. Access®.

Many people are familiar with using spreadsheets, but less so with the scope of the spreadsheet application itself, be it Microsoft Excel® or another program. Most spreadsheet programs are very powerful, however most standard spreadsheets use a mere 10% of their capability. It is important to realize that you can probably do anything you can dream up, provided of course that you have enough spreadsheet skills.

So you have established that you need a spreadsheet.

Example Spreadsheet

What is the first step?

What is the first step?

Firstly one must understand the “need”. If the final spreadsheet is to be of real benefit, then it is fundamental to consider this “need” during the planning stage. Certainly if you ensure you understand the process in detail prior to creating the spreadsheet you will be three quarters of the way there.

Other things to consider

Do I need to start from the beginning, or do I have access to an existing spreadsheet which can be manipulated to deliver the solution I require?

What will be the benefits?

Many routine tasks can be more efficiently carried out using an updated spreadsheet. Often we can continue to carry out very labor intensive processes, because this is the way we have always done things, and cannot always see that there is an easier way of arriving at the same result. Stand back from the problem and plan what you would like the spreadsheet to achieve, it is almost certain that the application will be man enough to do what you have only dreamed of before.

Finally a little advice

Automation

Are you a “copy/paste” expert, this type of functionality can be automated. By streamlining your tasks you could make the result more accurate, more time efficient and ensure your business optimizes its position in the market in these competitive and “cost conscious” times.

Keep it simple

While you can probably do almost anything you can dream up with your spreadsheet, it is also important not to be too ambitious at the beginning, or you will spend several days using “help”. Sometimes “help” can only assist if you know which questions to ask, or at least how to ask those questions. Aim slightly out of your comfort zone, and try and introduce only one or two unfamiliar functions to start with and I guarantee you will learn some really useful functions as you go along, and more importantly I think you may well begin to enjoy yourself.

An example of what can be achieved with a spreadsheet

\”Spreadsheet Demo on YouTube\”

All pictures and examples have been provided by SpreadsheetNow.com.

or try Clouds on the Internet

, , , , , , , , , , 131 Comments more...

Copyright © 1996-2010 Eric and Hazel's thoughts. All rights reserved.
iDream theme by Templates Next | Powered by WordPress