Formula

[xxsalahxx]

Hello I have a spreadsheet which has quite a lot of data in it .

I would like it to be able when adding WD 1, WD 2, WD 3, WD 4 into column A that Column B auto populates to say Medical and for Column C to say Medicine is this possible?
and WD 5 WD 6 WD 7 to say Surgical in column B and Surgery in column C

Is this possible?
Thanking you in advance for any help is much appreciated.

Input	OutPut	OutPut1
Ward 1	Medical	Medicine
Ward 2	Medical	Medicine
Ward 2	Medical	Medicine
Ward 3	Medical	Medicine
Ward 4	Medical	Medicine
Ward 4	Medical	Medicine
Ward 5	Surgical	Surgery
Ward 6	Surgical	Surgery
Ward 7	Surgical	Surgery

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[xxsalahxx]

Im using Excel 2007, thank you I have also updated this in my account details.

 

[Fluff]

Thanks for that
How about

Fluff.xlsm
	A	B	C
1	Input	OutPut	OutPut1
2	Ward 1	Medical	Medicine
3	Ward 2	Medical	Medicine
4	Ward 2	Medical	Medicine
5	Ward 3	Medical	Medicine
6	Ward 4	Medical	Medicine
7	Ward 4	Medical	Medicine
8	Ward 5	Surgical	Surgery
9	Ward 6	Surgical	Surgery
10	Ward 7	Surgical	Surgery
11
Main

Cell Formulas
Range	Formula
B2:B11	B2	=IF(A2="","",IF(RIGHT(A2)+0<=4,"Medical",IF(RIGHT(A2)+0<=7,"Surgical","?")))
C2:C11	C2	=IF(A2="","",IF(RIGHT(A2)+0<=4,"Medicine",IF(RIGHT(A2)+0<=7,"Surgery","?")))

 

[xxsalahxx]

Thank you so much that worked perfectly, much appreciated, enjoy your day.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[xxsalahxx]

Would I be able to expand on this so for instance at the bottom of the chart i now have ward I have ward 39 and ward 49?

Input	OutPut	OutPut1
Ward 1	Medical	Medicine
Ward 2	Medical	Medicine
Ward 2	Medical	Medicine
Ward 3	Medical	Medicine
Ward 4	Medical	Medicine
Ward 4	Medical	Medicine
Ward 5	Surgical	Surgery
Ward 39	Medical	Medicine
Ward 49	Surgical	Surgery

 

[Fluff]

Change it from RIGHT(A2) to RIGHT(A2,2) and change the criteria to suit.

 

[xxsalahxx]

Thanks again!!

 

[Fluff]

My pleasure.