[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Querying an Excel 2010 Sheet as a Database

Posted on 2014-01-08
4
Medium Priority
?
280 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 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

656 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