Solved

Querying an Excel 2010 Sheet as a Database

Posted on 2014-01-08
4
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

691 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