开发者

How do I lookup a particular cell in another worksheet dependent on the value of a cell?

I am trying to read in a table called operations that looks like so

"id";"name";
"1";"LASER CUTTING";
"2";"DEBURR";
"3";"MACHINING";
"4";"BENDING";
"5";"PEM";
"6";"WELDING";
"7";"PAINT PREPARATION";
"8";"PAINTING";
"9";"SILKSCREEN PREPARATION";
"10";"SILKSCREEN";
"11";"ASSEMBLY - PACKAGING";
"12";"LASER PREP";

I want to have a column in a worksheet that gets the appropriate name based on value of an operation_id column in another worksheet. How do I lookup a 开发者_JAVA技巧particular cell in another worksheet dependent on the value of a cell?

Example

userid, operation_id, operation_name
bob, 3, MACHINING


You should look at the DGET(database,field,criteria) function, reference here.

Or you can use this worksheet function:

VLOOKUP(cellWithID, Sheet2!A1:B13, 2, FALSE)

where cellWithID is the cell with the ID value you want to use.


Maybe the Lookup() function would work better for you. http://www.techonthenet.com/excel/formulas/lookup.php


Basing off this: "What I really want to do is just lookup the name for an operation without having to run a sql query every time or have an ugly huge if statement in every cell."

I guess what confuses me here is why you don't just use a join. You can always join that table as a lookup to whatever your sql statement is;

select operations.name, tableA.* from tableA 

left outer join operations on operations.id = tableA.operationid

If you wanted to, you could functionize this; not recommended. Subqueries are generally speaking bad news. However,

create function dbo.LookupOperationName
(
@id int
)
returns varchar(100)
as
declare @returnvalue varchar(100)
select @returnvalue = name from Operations where id = @id

return @returnValue

would do the trick. Then you could:

 select tablea.*,LookupOperationName(operationid) from tablea

Again, remember that your join example is much more performant. You could also create a view that had the join, and use the view in place of the table.... all kinds of things.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜