Improve company productivity with a Business Account.Sign Up

x
?
Solved

Querying an Excel 2010 Sheet as a Database

Posted on 2014-01-08
4
Medium Priority
?
285 Views
Last Modified: 2014-01-15
I have many Excel spreadsheets that I would like to query and I was wondering if there were any reasonable way to pick out information in a like manner as I do with a database product, maybe using some type of natural language questions, preferably with an English syntax. Firstly, I would like to explore the native capabilities of Excel. I realize that I could write some fairly complicated if then statements but I would prefer something simpler. The queries would probably be in a format similar to: if A1 is greater than A2, then multiply A1 by 15% and placed the answer in A5. Are there any commercial products that would allow querying of the if then type without writing complicated, long and unreadable commands? I'm open to any suggestions. Any help would be appreciated and points awarded. Thanks.
0
Comment
Question by:mzimerman
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:ChloesDad
ID: 39766332
You can write your own macros using VBA

In Excel 2010 click on the View menu and you will see Macro's, drop down on the menu

Click on record Macros and you will see a record macro page. Enter details on the page, these are not important yet, but it will allow us to get to another page, then click on OK.

Then drop down on Macro again and click stop recording.
Then drop down on Macro again and click View Macros, click edit on the Macro window that appears

You can then edit the macro to do what you want.

Using your example above you would do this

Dim CellA1Value
Dim CellA2Value
Dim CellA5Value

  CellA1Value = Range("A1").Value
  CellA2Value = Range("A2").Value
    
  If CellA1 > CellA2 Then
    CellA5Value = CellA1Value * 1.15
    Range("A5").Value = CellA5Value
  End If

Open in new window


You could write it with just the IF statement

  If Range("A1").Value > Range("A2").Value Then
    Range("A5").Value  = Range("A1").Value * 1.15
  End If

Open in new window


but I prefer to write things out long handed as it can make it clearer
0
 

Author Comment

by:mzimerman
ID: 39769577
This might work but the records that I use have about 1500 elements per record, many of which need inter-relating calculations. It could require an enormous amount of scripting. Sounds intimidating.
0
 
LVL 15

Accepted Solution

by:
ChloesDad earned 2000 total points
ID: 39769594
It can be written using a for next loop if there is a consistency to what you require.
0
 

Author Closing Comment

by:mzimerman
ID: 39783340
Kind of disappointed that this was the only response, but it was a good response.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This tutorial is about creating a new Microsoft Online User Profile account along with how to transfer your files and settings. You may be faced with this situation if your existing user profile has become corrupted.
You can use the network upload option and the Office 365 Import service to bulk-import PST files to user mailboxes. Network upload means that you upload the PST files a temporary storage area in the Microsoft cloud.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question