Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Dynamic Cell Values (Read 2520 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Dynamic Cell Values
27.04.10 at 10:33:36
 
Dynamic Cell Values
 
Quote:

I am interested in purchasing some SQL*XL licenses for our department, but am unsure if the software contains the function I am looking for.

Essentially, I want to enter a value in a cell (anywere in column A), and once entered, have a routine that will automatically take that value, and issue a SQL query with the cell value as a WHERE statement. The resulting information will then be displayed in the following columns. I need support for all cells within column A.

I might not be explaining it very well, so please let me know if more information is needed. If SQL*XL will provide the support we'll be looking at purchasing <snip> licenses.

 
Working with cells as parameters is not a problem. I think that a combination of an if function and the SQL*XL function sqlqueryvalue will solve the problem.  
 
I have put an example to demonstrate how that would work.  
 
In this example I will build a workbook that will lookup some detail columns given a value typed in column A. I am using the Publishers table in the biblio access database for the example.  
 
Initial task:
Retrieve the publisher name when the publisher id (pubid) is given in column A.
First, experiment with SQL where the pubid is hard coded. In a cell, e.g. C1, type the following formula:
=sqlqueryvalue("select name from publishers where pubid=1")
Display value: SAMS
 
To take the value for pubid from column A I am changing the SQL to:
=sqlqueryvalue("select name from publishers where pubid=" & A1)
 
Copy this formula to C2, C3, ... C10 and you will see that the cell address to column A is also moved down. C10 contains:
=sqlqueryvalue("select name from publishers where pubid=" & A10)
 
Retrieve more than one detail value:
In practise you often want to retrieve more than one detail column. In the following example I will retrieve 2 detail columns: Name and city. In principle you can use this technique to retrieve many more columns.
 
The technique I employ is that of Excel array formula. It is a little trick to define one formula (in our case one SQL statement) to a range of cells. Just select a set of cells and type one formula. Then press CTRL+SHIFT+ENTER to define the array formula. When you apply the formula this way Excel will display curly brackets around the formula in the formulabar: {=....} to indicate this is an array formula. If you are not familiar yet with array formulas in Excel I suggest you to study this useful technique.
 
SQLQueryValue is a function that can be used as array formula. You can retrieve more than one column and you can even return more than one row. For the purpose of this example I will retrieve 2 columns and 1 row.
 
Select cells C1 and D1. Enter the formula:
=sqlqueryvalue("select name, city from publishers where pubid=A1")
Press CTRL+SHIFT+ENTER to create the array formula
 
Now select C1 and D1, copy and paste them to C2:D10. The table neatly fills with the data. How easy that went!
 
But...if
As a final tweak I recommend you to create an if statement round the SQLQueryValue function so it does not execute when there is no value in column A. This avoids unnecessary calls to the database.  
 
Just a simpel if statement does the trick. The Excel syntax is as follows:
=if( condition , true, false )
 
We will use it to display a blank ("") when there is a blank in column A and to run the SQL when there is a value in column A:
=IF( ISBLANK(A1) , "" ,sqlqueryvalue("select name, city from publishers where pubid=" & A1))
 
Interactive:
It may not be obvious from the above but this solution is a fully interactive solution. Type a different id in column A and the query in that row will rerun automatically and the new values will be displayed.
 
See also:
SQLQueryValue video demo
http://www.oraxcel.com/projects/sqlxl/demos/database_query_cell_formula.html
Back to top
 
« Last Edit: 28.04.10 at 09:36:47 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Array formulas
Reply #1 - 28.04.10 at 09:36:04
 
Array formulas
 
Quote:
Thanks again, Gerrit-Jan. This is very helpful and appreciated. I will take your advice and also catch myself up on array formulas as well. I think I have established the base functionality we'll need. Now I just need to clean up my approach and improve my performance.

I really appreciate you taking the time to familiarize me with SQL*XL. I've been very impressed with your product thus far and look forward to working with it more. I believe it will really help us out with our initiatives.

 
Thank you for the nice words. I have added a link to a video demo that demonstrates the SQLQueryValue function.
 
I have put a few pointers below to topics that cover array formula:
 
Using Array Formulas in Excel  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1150621743
Excel array formulas that give an array as result  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1150622308
SQLQueryValue as Array formula  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1162290970
Back to top
 
« Last Edit: 28.04.10 at 09:37:14 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1