Solved

Querying an Excel 2010 Sheet as a Database

Posted on 2014-01-08
4
234 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 500 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now