Monitoring cell content & data validation

[KoE]

Greetings,

I'm reading J.Walkenbach's Excel 2007 PP with VBA on monitoring a range to validate data entry but could not make a sense to help me with this lil' problem

I've multiple worksheets in which column D contains text string (post, location & grade) & column E contains the corresponding grade only. Data entry into col E is "restricted" by means of dropdown list (from another worksheet named DropDownList). Sample of data in those worksheets is as below:

Excel Workbook
	D	E
1	Tempat	Gred
2	Timbalan Pendakwa Raya Unit Pendakwaan Negeri Melaka (Mahkamah Sesyen Melaka) Gred L41	L41
3	Timbalan Pendakwa Raya Unit Pendakwaan Negeri Melaka (Mahkamah Sesyen Melaka) Gred L41 (Kontrak)	L41 (K)
Sheet

and the dropdown list/range labelled Gred which is in a worksheet named DropDownList is as below:

Excel Workbook
	J
1	L41 (K)
2	L41
3	L44 (M)
4	L44
5	L48 (M)
6	L48
7	L52 (M)
8	L52
9	L54 (M)
10	L54
Sheet

I'm using dropdown in E currently to save time & minimise typing error as at times other people will be updating these data (not to mention that this is the only step so far that i know LOL)

Q:

How could I have cells in column E compare/reflect the grade in column D?

 

[xld]

Tricky. If there were a hard and fast rule you could use a formul to extract it, but how for instance do you know that the second one uses just K from the text?

 

[KoE]

xld,

in the sample, cell E3 [L41 (K)] is actually an abbreviation from part of the text string in cell D3 [L41 (Kontrak)].

At the moment, there are only instances of L41 and L41 (Kontrak) in all cells in column D. However, soon there will be L44 (Memangku), L48 (Memangku), L52 (Memangku) and L54 (Memangku) in column D across multiple worksheet that need to be converted to L44 (M)...L54 (M) respectively.

The string in col D is "controlled" such that they will always have Gred Lxx or Gred Lxx (Kontrak) or Gred Lxx (Memangku) at the end of the string.

 

[xld]

This might work for you

=MID(D2,SEARCH("Gred ",D2)+5,SEARCH(" ",D2,SEARCH("Gred ",D2))-SEARCH("Gred ",D2)-1)&
IF(ISNUMBER(SEARCH("(",D2,SEARCH(" ",D2,SEARCH("Gred ",D2))+1)),MID(D2,SEARCH("(",D2,SEARCH(" ",D2,SEARCH("Gred ",D2))+1),2)&")","")

 

[KoE]

xld,

thanks man...it runs perfectly