**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