I have never done any programming in excel, I am a pretty experience
programmer but here is my dillema. I need a spreadsheet, that when you
type a value in a cell, it looks up the value in a database (it is the
primary key of the table) and populates other columns in that row with
the data associated with that value. Is this something feasable to
program in excel? If it is, how would I go about getting this to work.
Thanks.

-Jon Williamson


--
jwilliamson
------------------------------------------------------------------------
jwilliamson's Profile: http://www.excelforum.com/member.php...o&userid=34493
View this thread: http://www.excelforum.com/showthread...hreadid=542593

Try DataExternal DataCreate Query

Search Help for Parametrized Query

HTH
--
AP
"jwilliamson" a
écrit dans le message de news:
...

I have never done any programming in excel, I am a pretty experience
programmer but here is my dillema. I need a spreadsheet, that when you
type a value in a cell, it looks up the value in a database (it is the
primary key of the table) and populates other columns in that row with
the data associated with that value. Is this something feasable to
program in excel? If it is, how would I go about getting this to work.
Thanks.

-Jon Williamson


--
jwilliamson
------------------------------------------------------------------------
jwilliamson's Profile:
http://www.excelforum.com/member.php...o&userid=34493
View this thread: http://www.excelforum.com/showthread...hreadid=542593

Most would prepopulate the cell with (assume the value is entered in A1 -
then in B1)

=if(A1="","",vlookup(A1,Sheet3!A:M,3))

If you mean a database like Access or SQL Server rather than a "table" on a
worksheet, then you could retrieve the data by using the Change event as a
trigger.

http://www.cpearson.com/excel/events.htm for an overview of what events are
available.

--
Regards,
Tom Ogilvy


"jwilliamson" wrote:


I have never done any programming in excel, I am a pretty experience
programmer but here is my dillema. I need a spreadsheet, that when you
type a value in a cell, it looks up the value in a database (it is the
primary key of the table) and populates other columns in that row with
the data associated with that value. Is this something feasable to
program in excel? If it is, how would I go about getting this to work.
Thanks.

-Jon Williamson


--
jwilliamson
------------------------------------------------------------------------
jwilliamson's Profile: http://www.excelforum.com/member.php...o&userid=34493
View this thread: http://www.excelforum.com/showthread...hreadid=542593

I understand how to make a query for myself, but I need to prepare this
spreadsheet in such a way that the user can open it, in column A type
all of the primary key values they want to see data for, and have it
fill in columns B-I with the data from the database about that primary
key value. So that they can print it and show it to other managers. How
would I go about doing that? Is it possible?


--
jwilliamson
------------------------------------------------------------------------
jwilliamson's Profile: http://www.excelforum.com/member.php...o&userid=34493
View this thread: http://www.excelforum.com/showthread...hreadid=542593

Now you need some VBA code.
You can use ADODB

HTH
--
AP

"jwilliamson" a
écrit dans le message de news:
...

I understand how to make a query for myself, but I need to prepare this
spreadsheet in such a way that the user can open it, in column A type
all of the primary key values they want to see data for, and have it
fill in columns B-I with the data from the database about that primary
key value. So that they can print it and show it to other managers. How
would I go about doing that? Is it possible?


--
jwilliamson
------------------------------------------------------------------------
jwilliamson's Profile:
http://www.excelforum.com/member.php...o&userid=34493
View this thread: http://www.excelforum.com/showthread...hreadid=542593

I understand that I will need to do some coding, I just need to know
where to start. I have all the data I need in my query, how do I get
the query to use Cell A1, A2, A3... and so on to be a parameter for
that query. So that when the user types a value in A1, B1-I1 are filled
with the data from the query, and the same for A2, A3, etc...


--
jwilliamson
------------------------------------------------------------------------
jwilliamson's Profile: http://www.excelforum.com/member.php...o&userid=34493
View this thread: http://www.excelforum.com/showthread...hreadid=542593

Are you asking how to concatenate values into a string to be passed as the
SQL command for the query.

sSQL = "Select name from table1 where field1 = '" &
worksheets("Sheet1").Range("A1").value & "'"

or something of that form.

--
Regards,
Tom Ogilvy


"jwilliamson" wrote:


I understand that I will need to do some coding, I just need to know
where to start. I have all the data I need in my query, how do I get
the query to use Cell A1, A2, A3... and so on to be a parameter for
that query. So that when the user types a value in A1, B1-I1 are filled
with the data from the query, and the same for A2, A3, etc...


--
jwilliamson
------------------------------------------------------------------------
jwilliamson's Profile: http://www.excelforum.com/member.php...o&userid=34493
View this thread: http://www.excelforum.com/showthread...hreadid=542593

Ok...let me try to explain again. We have a production database, on our
SQL server. There is information in the database that the managers
would like to be able to get on a spreadsheet. They want to be able to
open the spreadsheet, click cell A1 and type in a primary key value.
When they click another cell, press enter, or whatever the spreadsheet
will use the value they typed in A1 and put it in the where clause of a
query, that will populate cells B1-H1 with data from the table in the
database where the primary key field equals the value they typed in A1.
They want to be able to do this in any row of the spreadsheet, A1, A2,
A3, etc...How can I create this spreadsheet/query so that it will work
how they want it?


--
jwilliamson
------------------------------------------------------------------------
jwilliamson's Profile: http://www.excelforum.com/member.php...o&userid=34493
View this thread: http://www.excelforum.com/showthread...hreadid=542593

http://tinyurl.com/kac4o

--
Regards,
Tom Ogilvy


"jwilliamson"
wrote in message
...

Ok...let me try to explain again. We have a production database, on our
SQL server. There is information in the database that the managers
would like to be able to get on a spreadsheet. They want to be able to
open the spreadsheet, click cell A1 and type in a primary key value.
When they click another cell, press enter, or whatever the spreadsheet
will use the value they typed in A1 and put it in the where clause of a
query, that will populate cells B1-H1 with data from the table in the
database where the primary key field equals the value they typed in A1.
They want to be able to do this in any row of the spreadsheet, A1, A2,
A3, etc...How can I create this spreadsheet/query so that it will work
how they want it?


--
jwilliamson
------------------------------------------------------------------------
jwilliamson's Profile:
http://www.excelforum.com/member.php...o&userid=34493
View this thread: http://www.excelforum.com/showthread...hreadid=542593