Hi

If that's the case, then maybe you would be better off with a Vlookup.
Create a table on another Sheet (Sheet2) with your 30 or so items in
A1:A30.
Alongside each set the values in B1:B30 that you want assigned to each
Mark the block of A1:B30 and in the small white pane above row number
and left of column A (the name box) type myData and press Enter.

On your Sheet1, in cell B1
=IF(A1="","",IF(ISERROR(VLOOKUP(A1,myTable,2,0))," ",VLOOKUP(A1,myTable,2,0)))
Copy down column B as far as required.
Now anything typed in A1, will return the value associated with that
item.
--
Regards

Roger Govier


"ah" wrote in message
...
Sorry, I'm totally lost now.
Can anyone guide me on how to go about this?

I want the system to assign a value based on the following pre defined
table:
If Cell A1 contains the word called "Cost Center", then assign the
value 0.5
to me

If Cell A1 contains the word called "Department", then assign the
value 1.0
to me

If Cell A1 contains the word called "Bank", then assign the value 1.2
to me
and etc

FYI, I have about 30 criterias to fulfill.
What are the best approach that I can use?

thanks in advance

Hi;
Thanks for your reply.

Yup, "cost center" is just part of the word.
For example: US cost center - international, UK cost center - local
and etc

"Toppers" wrote:

Does A1 only contain the text you want to test or is a sub-set e.g it
could
contain "XYZ Cost Center"?

"ah" wrote:

Hi;

Thanks for your help.
But, I'm confused now. Let me tell you what I want so that you can
assist me:

I want the system to assign a value based on the following pre
defined table:
If Cell A1 contains the word called "Cost Center", then assign the
value 0.5
to me
If Cell A1 contains the word called "Department", then assign the
value 1.0
to me
If Cell A1 contains the word called "Bank", then assign the value
1.2 to me
and etc

FYI, I have about 30 criterias to fulfill.
What are the best approach that I can use?
Please advice.
Thanks in advance

"Toppers" wrote:

There is a limit of 7, although to can have work around.

Look at VLOOKUP as an alternative solution: create table in
column A & B
starting row 2

A B
Cost Center A
OM B

etc

=VLOOKUP(E12,$A$2:$A$10,2,0)

For error condition:

=IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not
OK",VLOOKUP(E12,$A$2:$A$10,2,0))

HTH

"ah" wrote:

Hi;

Is there a limit of the if else criteria that I can put in for
excel? I
found that it doesn't allow me to put in more than 7 criterias.


"ah" wrote:

Hi;

I get an error when I use the following statement:

=IF(ISNUMBER(SEARCH("Cost
Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank
Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports
To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not
OK")))))))

The error message is as follows:
The formula you type contains an error

Can anyone help me with this?