Linker IT Software
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
Making a Value lookup (Read 2074 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Making a Value lookup
08.12.09 at 11:04:43
Making a Value lookup
My requirement is as below:
There is a table S_LST_OF_VAL which has two columns NAME and VALUE.

Now i have data in the excel sheet ( test.xls)
where there are 3 columns A, B, C
In column 'A' of the excel sheet we have the data which is same as that of the NAME column of S_LST_OF_VAL table,
what i need to do is for the values in column A, I need to look up in the S_LST_OF_VAL table and get the corresponding VALUE
from the table into the excel sheet.

Hope that makes it clear in regard with what i want to achieve.

Yes, that is clear! What you want is easy to achieve through the use of the SQLQueryValue function.
Suppose your spreadsheet looks like this:
A1: one
A2: two
A3: three
A4: four
And suppose your table translates them into a numerical. If you want the result to be displayed in column B then type in B1 the following formula:
B1: =SQLQueryValue("select value from S_LST_OF_VAL where name = '" & A1 & "'" )
Note there is a little work with the single quotes in the formula. Basically I am wrapping the value in A1 in single quotes.
If you enter the formula in B1 you should see the value 1 displayed immediately.  
Now copy B1 and paste it to B2 to B4. You will see that the formula automatically adjusts.
The table now looks like this:
         A         B
1       one       1
2       two       2
3      three     3
4      four       4
Back to top

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