Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .