Lookup Cell contents and return value based on what is in the cell

Hi,

I am sure this is a simple one but cant quite get my gear around it, firstly i need to set the conditional formatting in the first cell (A14) so that users can only input a certain string of a Project Number eg, PJ-06-0099-0001-09 or PJ-03-0077-0101-01 so that i always end up with (PJ-##-####-####-##)

Secondly i need another cell (J14) to look at the first cell (A14) and and from the the first set of numbers PJ-03, PJ-04, PJ-05 etc, display a number for the transaction code, T1, T2, T3 etc

T1 – PJ-03: Sales & Marketing
T2 – PJ-06: Installation & Commissioning
T3 – PJ-08: Chargeable Service Work
T4 – PJ-05: Warranty
T5 – PJ-02: Office Support, training, medical, holidays etc
T7 – PJ-04: R & D work

So PJ-06-0101-0099-00 would return a value of T2 and PJ-04-0099-0001-08 would return a value of T7

Hope i am making sense

I think you need data validation, not conditional formatting for the first part.

For the second part you can use this formula in J14:

=LOOKUP(LEFT(A14,5),{"PJ-02","PJ-03","PJ-04","PJ-05","PJ-06","PJ-08"},{"T5","T1","T7","T4","T2","T3"})

Hope this helps.

Pete

Thanks Pete
Is there a way to not showing anything if the A14 cell is blank, at the moment i get #N/A if there is no data in cell A14

As regards to Conditional Formatting, what i actually meant was Custom Formatting. with Data Validation, this only allows me to determine what type of data is added and throw up an error if it is not correct what i really want is, for the user to be able to add pj-8-009-01-02 or even pj.8.009.01.02 and the cell to change it to PJ-08-0009-0001-02, does this need to be bone in Custom Formatting ??

You can do this to avoid #N/A if A14 is empty:

=IF(A14="","",LOOKUP(LEFT(A14,5),{"PJ-02","PJ-03","PJ-04","PJ-05","PJ-06","PJ-08"},{"T5","T1","T7","T4","T2","T3"}))

Custom Formatting only applies to numbers, not text values, so although you could have the "PJ-" as part of the custom format so that users only have to put in numbers into that cell, they would need to enter 80009000102 and not numbers mixed with decimal points (and leading zeros omitted). Apart from that, the formula that I gave you wouldn't work, as the "PJ-" wouldn't be part of the value in that cell.

Hope this helps.

Pete