Excel Gurus, I need help

[z0mb13]

Ok I have a new question... so lets say that I have these ID fields in column A:

21345
76351
87366
9286

In column B, I want a string to be placed automatically according to this rule:
if the first number in ID field starts with 2, the string will be San Francisco
if the first number in ID field starts with 7, the string will be Los Angeles

SO on and so forth..

So should I Just use nested IF statements? is there a smarter way to do it?

THanks

 

[CaseTragedy]

tools > protection > protect sheet
enter password and options

by default ALL cells are locked--so you'll need to unlock the cells you want to be modified (format cells > protection)

 

[z0mb13]

bump for new question

 

[CaseTragedy]

Originally posted by: z0mb13
So I am inputting some data, one of the fields is ID key.

Can I make it so that whenever I enter an ID key, excel will check the ID keys that are already there, and give out a signal if a particular ID is already present?

for instance:

Key
24
21
98
21

Then I input 24 again, I want excel to give out a signal saying that 24 is already there.

Thanks for your help guys!

Will the field be on the same worksheet as the stored IDs?

 

[WannaFly]

Gah curse not having office on my machine, i know howto do this! If you havent gotten an answer by the time i am at work tomorrow i'll post an answer for you.

 

[CaseTragedy]

Well anyways--assuming it's all on the same sheet--just use data validation.

Example:
*Column A has all the existing IDs
**Cell B1 is the field that you want to validate
On B1--Data > Validation
Settings > Allow: Custom, Formula: =COUNTIF(A:A,B1)=0
Error Alert > Input your error message
Okay

 

[z0mb13]

thank u much!

 

[z0mb13]

bump for new question

 

[NeoV]

how many different areas do you want to have?

You could set up a little table - on a different worksheet, or in some hidden columns, that has all your data - first column is just numbers...1 thru 100 (or however many different cities you want to have), and the second column, right next to it, is the city name....for this example lets say its E1:f99

then, in your column B from the example, you could just do a vlookup that would look like this:

=vlookup(left(a1,1),e$:f$99,2,false)

That would do the trick...

 

[rainypickles]

you should leave the previous questions there so people can see them. and that way, it keeps the thread coherent

 

[TSDible]

To answer your current question (I think).

It would be better to use a table with a lookup statement so that your IF statement doesn't get too hard to manage.