autofill cell depending on text in another cell

A

Acehole

Board Regular
Joined
Sep 29, 2009
Messages
249
hello all, the noob is back for help if possible.
I have a cell (A13)that is populated by drop down list. the text reads for alternative treatment
if cell A13 has the word alternative in it I would like cell E13 to autofill with the numbers 180103* if that is possible
hope that makes sense
regards
acehole
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Does this formula in E13 do what you want? If not, more details please.

=IF(COUNTIF(A13,"*alternative*"),"180103*","")
 
Upvote 0
hi there and thanks for helping
of course it worked, as most things from this board always do.
how would I expand on this for other scenarios, as in changing the number for other key words, assume A13 could also have the text cytotoxic I would then need the number to be 180108* if it contained offensive then 180104 required. it may be getting to complex now
thanks again
acehole
 
Upvote 0
hello again, I have tried to use the following but it will not work =IF(COUNTIF(Q13,"3291"),"Clinical Waste, Unspecified, N.O.S",""), IF(COUNTIF(Q13,"3249"),"Medicines, Solid Toxic, N.O.S","") thanks acehole
 
Upvote 0
Acehole said:
how would I expand on this for other scenarios, as in changing the number for other key words, assume A13 could also have the text cytotoxic I would then need the number to be 180108* if it contained offensive then 180104 required.
Click to expand...

Acehole said:
hello again, I have tried to use the following but it will not work =IF(COUNTIF(Q13,"3291"),"Clinical Waste, Unspecified, N.O.S",""), IF(COUNTIF(Q13,"3249"),"Medicines, Solid Toxic, N.O.S","") thanks acehole
Click to expand...
I'm a little confused we seemed to be talking about A13 and words like "alternative", "cytotoxic" and "offensive". Now it appears to be Q13 and I'm not sure if the 'words' we are looking for in that cell are "3291" and "3249" or "Clinical Waste, Unspecified, N.O.S" and "Medicines, Solid Toxic, N.O.S".

Please clarify and also tell us how many "words" are to be looked for as the technique for looking for 2 or 3 "words" would be different to looking for 20 "words".

Also, when you said "..if cell A13 has the word alternative in it..", did you mean that A13 would only have the word "alternative" or that A13 might contain something like "The alternative to red is blue"?
 
Last edited:
Upvote 0
wow, apologies for the confusion, again my lack of talent letting me down , even the ability to explain myself has disappeared ill try to explain a little better if I can. there will be more than a couple of words to look for so I am guessing I cant do it with just using the IF statement. A13 could contain up to 9 words to be looked for and cause the number change in E13. again thank you for your time and patience
 
Upvote 0
Acehole said:
A13 could contain up to 9 words to be looked for and cause the number change in E13.
Click to expand...
But what about this question, which applies to all 9 words.
Peter_SSs said:
Also, when you said "..if cell A13 has the word alternative in it..", did you mean that A13 would only have the word "alternative" or that A13 might contain something like "The alternative to red is blue"?
Click to expand...
 
Upvote 0
hello,
ta for the reply,
their will be only 1 instance of the word in the cell but yes other words too
thanks
acehole
 
Upvote 0
Try something like this. I've created a table of the relevant words and values in columns J:K

Excel Workbook
ABCDEFGHIJK
1
2alternative180103
3cytotoxic180108
4offensive180104
5
6
7
8
9
10
11
12
13the offensive has started180104
Acehole




:eek: Warning: This suggestion as posted could possibly lead to incorrect results. For example, if A13 contained "What offensive?" then E13 would be blank.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Worksheet Event adding and removing Data Validation based on multiple Conditions
Replies
0
Views
234
Nathan Asius
N
M
  • Question
Formula to populate the next available cell in a column using data populated in another cell.
Replies
4
Views
308
Marshkevuk
M
A
  • Question
How to auto-populate a number entered in one cell to another depending on date range
Replies
9
Views
89
awoohaw
awoohaw
J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
203
Jeffrey Mahoney
Jeffrey Mahoney
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
428
MissDB101
M

Forum statistics

Threads
1,214,606
Messages
6,120,487
Members
448,967
Latest member
visheshkotha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top