Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using an excel sheet to automate the process of calculating fiber dataloss.

The problem I have is the following:

I am using the values in the "Soorten Vezels" part of this sheet to generate a dropdown menu in another part of the worksheet. To keep this excel file really customizable, I want to be able to add a new type of fibre and then just add the max allowed loss next to it.

Soorten Vezels

Somewhere else in this sheet, I will be able to choose the type of fibre in a dropdown menu and then the excel sheet will automatically enter the max loss ( the one next to it in the pre-made list) in another field next to it.

I hope I have made this clear enough

Edit:

By using this:

=OPZOEKEN([Type kabel];Tabel3[#alles])

with "type kabel" will be the value wich I am looking for in tabel3 and this works. However, if I do the same in another field:

=OPZOEKEN(Tabel1[Type Connector];Tabel5[#alles])

it gives me the error:

#N/B

What Is wrong?

share|improve this question
    
I don't have Dutch to try this, but if your lookup array is Tabel1 and the value to be looked up is in F1 does =OPZOEKEN(F1;Tabel1;2;0) work? –  pnuts Jun 10 '13 at 18:10
1  
It does seem to work so far –  Gutanoth Jun 11 '13 at 5:38
add comment

1 Answer

up vote 0 down vote accepted

You can use a Vlookup function for that.

=vlookup(a1,Sheet2!$A$3:$B$9,2,False)

... where A1 is the cell with the drop-down value. Depending on your layout, you could build a dynamic range name for the data validation list and the lookup table, so you don't have to change your formula every time you add a new "Soorten Veezels". For the data validation list use the named range LookupList with a formula like

=Sheet2!$A$3:INDEX(Sheet2!$A$3:$A$9,MATCH("zzzz",Sheet2!$A$3:$A$9,1))

For the lookup table use the name LookupTable with the formula

=offset(LookupList,0,0,,2)

Then you can use =LookupList as the reference for the drop-down and use the vlookup like this

=vlookup(A1,LookupTable,2,false)
share|improve this answer
    
I get the following error: #N/B –  Gutanoth Jun 10 '13 at 12:42
    
@Gutanoth Apart from the function name, the usual 'catch' when using languages other than English is the separators (seems you need ; where the formulae above apply ,). –  pnuts Jun 11 '13 at 5:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.