I don't know what I need to do!

M

monkeydan

Hi guys,

I have no idea what I'm trying to do so I couldn't put it in the thread
title!

Basically I have a dropdown menu on a column from which you can choose
A, B or C

If you select A, I want 900 to appear in the adjacent cell

If you select B, I want 1,000 to appear in the adjacent cell

If you select C, I want 1,200 to appear in the adjacent cell.

So far I've got this... =IF(AA2="A","900","") but I can't figure out
how you can link these together so it will return a value for all 3
selections.

I think this is very simple and I'm sure I've done it before, but it's
driving me mad!

Thanks everyone!
 
B

Bob Phillips

=IF(AA2="A",900,IF(AA2="B",1000,1200))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
M

monkeydan

Wow, am I a dumbass or what?

=IF(AA2="A",900,IF(AA2="B",1000,(IF(AA2="C",1200))))

I have figured it out - I wasn't using the brackets properly and
re-using the 'IF' between each value.

So I have succeeded!
 
M

monkeydan

Hi Bob, thanks for your help; I guess we posted at the same time.

I have another question for everyone -

Can you format cells according to the value in another cell?

In the first cell you can select whether you want to choose a region i
the UK or a region in Ireland. If you select the UK, cells after tha
should be formatted with a £ sign. If you select Ireland, cells afte
than should be formatted with a EURO sign (which I can't seem to fin
on my keyboard!
 
M

monkeydan

And following on from the above, I want the formula in the second pos
to also contain EURO values if 'Ireland' is selected in the first cell
I guess this will use the AND function but as you are no doubt no
aware, I'm not too hot on Excel.

Thanks for any help you can give me
 
M

monkeydan

OK, disregard the two questions above as I'm now aware you can't format
cells to that degree using conditional formatting (although please feel
free to enlighten me if I'm incorrect!)

However, I now have another question...

I have a list of postcodes which correspond to a certain value (AB).

If cell O2 contains one of the postcodes in the list (on sheet 2), I
want cell AN to display 'AB'. If the postcode in O2 does not appear on
the list, I want cell AN to display "CD"

At the moment, I've got this formula in cell AN2...

=IF(O2=SHEET2!A2:A190),"AB","CD")

But everything is returning CD, regardless of whether cell O2 contains
a postcode within A2:A190 or not.

Can anyone help?!
 
M

monkeydan

I did that, but it still returns 'CD' even if the postcode in cell O2
corresponds to one of the cells in the range A2:A190 :confused:
 
B

Bob Phillips

=IF(ISNUMBER(MATCH(O2,SHEET2!A2:A190)),"AB","CD")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
D

Dave Peterson

Without reading the whole thread, it kind of sounds like there should be an
exact match:

=IF(ISNUMBER(MATCH(O2,SHEET2!A2:A190)),"AB","CD")
should be
=IF(ISNUMBER(MATCH(O2,SHEET2!A2:A190,0)),"AB","CD")

(and extra ",0" in the formula)
 
M

monkeydan

Thanks so much! That worked fine.

Is it possible, however, to make this conditional on the value i
another cell?

Basically I only want 'AB' or 'CD' to show if 'Yes' is selected from
dropdown I created in cell AL2. There are only 2 choices in this cell
'Yes' and 'No' - if 'No' is selected I want 'N/A' to appear in cell AN
(the one that shows 'AB' and 'CD'.

Does this make sense
 
D

Dave Peterson

I'm not sure I understand, but maybe...

=IF(ISNUMBER(MATCH(O2,SHEET2!A2:A190,0)),"AB","CD")

becomes

=if(al2="no","N/A",IF(ISNUMBER(MATCH(O2,SHEET2!A2:A190,0)),"AB","CD"))
 
Top