A formula can only change what is displayed in its own cell.
Also a cell can contain only one value, not a range such as D2:D8
Not clear what you have for data but based on your example it seems you need
=IF(A2="OMD", Sheet3!D2, "") as the formula in C2
=IF(A2="OMD", Sheet3!D3, "") in C3
To make it easier to copy from C2 to the other cells, start with
=IF($A$2="OMD", Sheet3!D2, "") as the formula in C2
Maybe tell us more about what you want
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"winklenugget" wrote in message
...
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!