Solved

link sql database field to column

Posted on 2014-10-06
12
101 Views
Last Modified: 2014-10-07
In column C we have the item number and in column D the description.  Is there a way to make a column C cell scrollable through a list of item numbers found in a database, and after you have found the item number you want column D shows the description for that item number.  The information we want to look at is in a sql database.
0
Comment
Question by:TomBalla
  • 5
  • 5
  • 2
12 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40363680
Just thinking quickly:  You could set up an external connection to your SQLServer db and import that field to another sheet.  Then set up data validation to point to that field/column so that it can be used on your master sheet here.
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40363682
There's a number of ways to do this.

I personally would go Data=>From Other Sources=>SQl Server

Enter your server details and choose your table or view.

Then when the data comes in and it asks how you want to import data (table, pivot etc.), click properties=>definition. Change command type from table to SQL, and then write a SQL statement using '?' where you want the parameter to be. Click ok, and then click properties again, and the parameters button should now be enabled.

You can then link the '?' in your query to a location in your spreadsheet. This location will be where you have setup a cell with a validation list containing all possible item numbers.

Hope this helps.
0
 

Author Comment

by:TomBalla
ID: 40363843
I'm trying to connect to the database, but it is not listed.  Several other databases are listed, but not the one I need.  Why would that be?
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 12

Expert Comment

by:James Elliott
ID: 40363847
You may need to just type it in.
0
 

Author Comment

by:TomBalla
ID: 40364780
Okay, I got the database table to show in the excel sheet, but it showing every field in the database table.  How do I just select two of the fields to show?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40364846
You'll change your connection Command Type from "Table" to SQL and then enter a SQL statement to just select the two fields.

1) Click any cell in your Excel Table.
2) Excel Menu:  Data - Connections
You'll see a new dialog box:
Workbook Connections dialog3) Click the "Properties" button
The Connection Properties dialog box will appear:
Connection Properties dialog4) Click the "Definition" tab
5) Change the Command type to "SQL"
6) Enter a SQL statement that selects just the two fields you wish,
7) Click the "OK" button
8) Click the "Close" button
If the table does not display only the fields requested, click the "Refresh all" Icon in the "Connections" section of the ribbon.

-Glenn
0
 

Author Comment

by:TomBalla
ID: 40365942
Alright, that's looking good.  Is there a way to show a stockcode more than once?  The report will sometimes need to look like this.

Griffins  45644     512VTF   Red Candle  
Johnny   45645     512VTF   Red Candle
Billy's     45646     510VTF   Blue Candle

Thanks.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40365951
I'm not quite sure what you're requesting now.  Going back to your original question, i would have thought you would set up a data validation rule so that the items in column C (stock code) would be limited to those resulting from the SQL connection.  Then, you could create a VLOOKUP formula in column D to return the description, based on the code shown in column C.

You may have to provide an example workbook if this is not clear to you.

Regards,
-Glenn
0
 

Author Comment

by:TomBalla
ID: 40366014
We want our loading dock to be able to scroll through our list of items and select an item number, and when they select that item number the description shows up in the next cell over.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40366220
Then, you would indeed set up data validation and a lookup formula in the cells in column C and D.

Since you have a SQL connection that resides in an Excel Table, you can use Structured Referencing to dynamically show the stock codes in a data validation pick list.  If you add, change, or remove stock codes from the table (i.e., the SQL table is updated), the Data Validation will automatically follow this.

You do this by highlighting all the cells in column C on your order sheet, then select Menu: Data -- Data Validation.  Change "Allow" to "List" then enter a formula like this in the "Source" box:
=INDIRECT("Table2[StockCode]")
where "Table2" is the name Excel has assigned your SQL table output and [StockCode] is the fieldname (you must include brackets and the double quotes around it all).  If you aren't sure of the table name, click any cell in the SQL table and then click Menu: Table Tools - Design.  Look on the top left of the contextual ribbon and you'll see the name in the box labeled "Table Name".

When that's done, you can then add a VLOOKUP formula to return the description.  In the first cell in column D, insert this formula and then copy down:
D2: =IFERROR(VLOOKUP(C2,Table2,2,FALSE),"")

This will display the description if a stock code is chosen in column C.

See the example workbook for more guidance.

Regards,
-Glenn
EE-DataValidation-Lookup.xlsx
0
 

Author Comment

by:TomBalla
ID: 40366444
Thank you very much for your time, Glenn.  I got it working.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40366744
Cool.  There are plenty of other methods available for dynamic data validation, but once you have a grasp of structured referencing for tables, it's pretty flexible.

-Glenn
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

825 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