IF formula
 

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!

IF formula
 

You need to use a VLOOKUP function, along the lines of:

=VLOOKUP(A2,Sheet3!A2:D8,4,0)

This assumes that you have a table in Sheet3 occupying cells A2 to D8,
and you want to return data from column D where the data in column A
matches exactly what you have in the drop-down cell.

Hope this helps.

Pete

On Sep 10, 12:14 pm, 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!

IF formula
 

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!

IF formula
 

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!

IF formula
 

Look at Pete UK's proposed solution - upon reflection, he may have offered a
better one if you want the results to just show up in C2, and not a series of
results in several cells on down the sheet.

"JLatham" wrote:

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!

IF formula
 

Thanks alot for your help with this. I've copied the formula you provided and
altered it accordingly for each corresponding cell and it works perfectly. If
you could help me with the following, I'll be able to complete what I want.

How do I add more conditions to the formula to provide different results for
another entry in A2 (e.g. changing OMD to M2M)?



"Bernard Liengme" wrote:

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!

IF formula
 

This is a task for VLOOKUP, IF is not suitable here.
On sheet4 in A1:D3 I have
MAT a 1 apple
CAT b 2 pear
DOG c 3 plum


In A1 of Sheet1 I have: CAT
In B1 I have the formula =VLOOKUP($A$1,Sheet4!$A$1:$D$3,2,FALSE) and it
displays: b
In C1 I have =VLOOKUP($A$1,Sheet4!$A$1:$D$3,3,FALSE), it displays: 2
In D1 =VLOOKUP($A$1,Sheet4!$A$1:$D$3,2,FALSE) displays: pear
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"winklenugget" wrote in message
...
Thanks alot for your help with this. I've copied the formula you provided
and
altered it accordingly for each corresponding cell and it works perfectly.
If
you could help me with the following, I'll be able to complete what I
want.

How do I add more conditions to the formula to provide different results
for
another entry in A2 (e.g. changing OMD to M2M)?



"Bernard Liengme" wrote:

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!