I assume that A2 is where your drop down list is.
In C2, put this formula:
=IF($A$2="OMD",Sheet3!D2,"")
you can now fill that formula on down through C3, C4, C5, C6, C7 and C8.

For the other possibilities (limit is 7 levels of nesting with IF), you add
more IF's to the formula for the not true conditions:
=IF($A$2="OMD",Sheet3!D2,IF($A$2="XYZ",Sheet3!E2,I F($A$2="ABC",Sheet3!F2,"")))

Again you could fill that formula down. The $A$2 makes that address
absolute and it won't change as you fill the formula, and with D2, E2, F2
being relative addresses (no $ signs) they will increase the row number as
they are filled down your sheet.

"winklenugget" wrote:

I've created a drop down list of companies and I want a formula that will
display the relevant details (entered over a number of cells) when I select
one of them. I've been using the below formula which I know is incorrect but
might give you an idea of what im trying to do:

=IF(A2="OMD", C2=Sheet3!D2:D8)

I know you have to enter a value if false aswell but I'm really stuck here.
Also I need to be able to select another value in A2 and have it return
different results.

Help please!