Solved

Querying an Excel 2010 Sheet as a Database

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

679 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