how do i set a conditional text in Excel?

[Guest]

Hello,,
how r u all,, i have a small prob. i hope u can help me with
i've created a drop down list in excel in one coloumn (the entire coloumn's
cells contains the same list). .. the next coloumn should show the ID of the
chosen data from the list. ID's r unique for each entery,, there are about 47
raws
how can i do that?

 

[JulieD]

Hi mona

it sounds like you need to use the VLOOKUP function in the cell to the right
of your drop down.

to do this you'll need a table somewhere else in the workbook which has the
values in the dropdown box and the ID's listed
e.g. Sheet 2
..........A..............B
1...Product......ID
2...Product 1....ID.1

etc
then in your vlookup formula use
=VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)
which means look up the value in A1 (ie the cell reference of the drop down
box) in the list in sheet 2 and when you find an exact match, return the
information from the second column of this table

Note, if you don't have a value in the cell that you're looking you'll get a
#NA error to overcome this use
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)),"",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0))

Cheers
JulieD

 

[Guest]

HELLO JULIED
thank you very much for your reply.. it really helped me understand more the
VLOOKUP command.. i also went back to Excel help and read the explaination
again..
i also applied the formula you gave me to the file i have.. and i did some
small changes in it to fill my need in the file.. but it still can't find the
ID, it seems there is small prob. i couldn't put the hand on..
Note: the first coulomn in sheet two has the data for the drop down list,
which is in the form of text.. the second coloumn contains the ID's which are
numerical.. is this the prob??
i'll be really gratefull if you answer this one..
thank you very much for your time
mona

 

[JulieD]

Hi Mona

text or numbers work the same in VLOOKUP

what is the sheet name & range of the drop down list
what is the sheet name & range of your two columns
what is your VLOOKUP formula

Cheers
JulieD

 

[Guest]

hi Julied,,
the sheet's name is sheet2 (havn't changed it), the drop down list starts
from G6 with out end limit , the two columns from A1 to B 43,
and my formula is:
=IF(ISNA(VLOOKUP(G6,Sheet2!$A$1:$B$100,2,0)),"",VLOOKUP(#REF!,Sheet2!$A$1:$B$100,2,0))


waiting
mona

 

[JulieD]

Hi mona

if it really says #REF change it to G6 in the second lookup

Cheers
JulieD

 

[Guest]

i did it now.. it now gives this sign #########

:s
what should i do?

 

[Guest]

oh god.. i feel so stupid,, this file is driving me crazy..
i guess i'll leave it for tom. anyway my working hours are over


hope to c u tom.
thank you again for every thing
c u
mona

 

[Dave Peterson]

Either widen that column or format it as General.

 

[Guest]

YEEEEEEEEEESSSSSSSSSS

IT WORKED

IT REALLY DID
YEEEEEEEESSSSSSS

THANK YOU ALL
YOU ARE REALLY HELPFUL
I OWN U ONE