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
 
Sort by date Sort by votes
Acehole said:
hello again, tried using the html thingy but was less readable than this ?
TEXTNUMBER REQUIRED
Infectious healthcare waste for alternative treatment 180103
Sharps contaminated with non-hazardous medicines180103
Cytotoxic waste (may contain potentially infectious material)180108
Non hazardous waste medicines180109
Infectious healthcare waste for incineration180103
Offensive waste180104
Hazardous chemical waste LQ 180106
Anatomical waste (potentially contaminated with formalin solution)180103
amalgam contaminated waste180110
dialysis waste (may contain potentially infectious material)180106
packaging contaminated with hazardous chemicals180106
potentially infectious waste contaminated with chemicals180106

<tbody>
</tbody><colgroup><col><col><col></colgroup>
hope this is clearer I will try to use the formats you suggested when I can make it work
regards
acehole

<tbody>

</tbody>
Click to expand...
The "html thingy" code looks like rubbish when you first paste, but once you post it turns into a good screen shot - as you have seen. Test in the Test Here forum.

Is that the column J:K lookup list or is that what might appear in A13 and E13? (I did ask you to post both ;))
Again, a good screen shot would have made my question unnecessary.
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
The "html thingy" code did look like rubbish, I will sort it out
yes the first list of text is what will appear in columns A13 TO E13.
regards
acehole
 
Upvote 0
Acehole said:
The "html thingy" code did look like rubbish, I will sort it out
yes the first list of text is what will appear in columns A13 TO E13.
regards
acehole
Click to expand...
So now I need to see what would be in columns J:K.
 
Upvote 0
Hello, you must be close to throwing in the towel now with me.
Excel 2010
ABCDEFGHIJK
3lookup listNUMBER REQUIRED
4Infectious healthcare waste for alternative treatment180103D09Infectious healthcare waste for alternative treatment180103
5Sharps contaminated with non-hazardous medicines180103D15Sharps contaminated with non-hazardous medicines180103
6Cytotoxic waste (may contain potentially infectious material)180108D15Cytotoxic waste (may contain potentially infectious material)180108
7Non hazardous waste medicines180109D15Non hazardous waste medicines180109
8Infectious healthcare waste for incineration180103D15Infectious healthcare waste for incineration180103
9Offensive waste180104D15Offensive waste180104
10Hazardous chemical waste LQ180106D15Hazardous chemical waste LQ180106
11Anatomical waste (potentially contaminated with formalin solution)180103D15Anatomical waste (potentially contaminated with formalin solution)180103
12amalgam contaminated waste180110D15amalgam contaminated waste180110
13dialysis waste (may contain potentially infectious material)180106D15dialysis waste (may contain potentially infectious material)180106
14packaging contaminated with hazardous chemicals180106D15packaging contaminated with hazardous chemicals180106
15potentially infectious waste contaminated with chemicals180106D15potentially infectious waste contaminated with chemicals180106
16
17K to populate E3 E4 E5 ETC
Sheet2
Cell Formulas
RangeFormula
J3lookup list
J4Infectious healthcare waste for alternative treatment
J5Sharps contaminated with non-hazardous medicines
J6Cytotoxic waste (may contain potentially infectious material)
J7Non hazardous waste medicines
J8Infectious healthcare waste for incineration
J9Offensive waste
J10Hazardous chemical waste LQ
J11Anatomical waste (potentially contaminated with formalin solution)
J12amalgam contaminated waste
J13dialysis waste (may contain potentially infectious material)
J14packaging contaminated with hazardous chemicals
J15potentially infectious waste contaminated with chemicals
J17K to populate E3 E4 E5 ETC
K3NUMBER REQUIRED
K4180103
K5180103
K6180108
K7180109
K8180103
K9180104
K10180106
K11180103
K12180110
K13180106
K14180106
K15180106
A4Infectious healthcare waste for alternative treatment
A5Sharps contaminated with non-hazardous medicines
A6Cytotoxic waste (may contain potentially infectious material)
A7Non hazardous waste medicines
A8Infectious healthcare waste for incineration
A9Offensive waste
A10Hazardous chemical waste LQ
A11Anatomical waste (potentially contaminated with formalin solution)
A12amalgam contaminated waste
A13dialysis waste (may contain potentially infectious material)
A14packaging contaminated with hazardous chemicals
A15potentially infectious waste contaminated with chemicals
E4180103
E5180103
E6180108
E7180109
E8180103
E9180104
E10180106
E11180103
E12180110
E13180106
E14180106
E15180106
F4D09
F5D15
F6D15
F7D15
F8D15
F9D15
F10D15
F11D15
F12D15
F13D15
F14D15
F15D15
 
Upvote 0
Yes, I am close. :eek:

You need to do some testing with that HTML Maker in the Test Forum so that you get it under control before using it too much in a real thread.

I have no idea what all that business earlier on about checking for "a word" or checking the first two words. From that screen shot, it seems a simple VLOOKUP will produce those results as you appear to be matching the whole cell in column A with the whole cells in column J.

Formula in E2, copied down.

Excel Workbook
ABCDEFGHIJK
3lookup listNUMBER REQUIRED
4Infectious healthcare waste for alternative treatment180103D09Infectious healthcare waste for alternative treatment180103
5Sharps contaminated with non-hazardous medicines180103D15Sharps contaminated with non-hazardous medicines180103
6Cytotoxic waste (may contain potentially infectious material)180108D15Cytotoxic waste (may contain potentially infectious material)180108
7Non hazardous waste medicines180109D15Non hazardous waste medicines180109
8Infectious healthcare waste for incineration180103D15Infectious healthcare waste for incineration180103
9Offensive waste180104D15Offensive waste180104
10Hazardous chemical waste ****LQ *180106D15Hazardous chemical waste ****LQ *180106
11Anatomical waste (potentially contaminated with formalin solution)180103D15Anatomical waste (potentially contaminated with formalin solution)180103
12amalgam contaminated waste180110D15amalgam contaminated waste180110
13dialysis waste (may contain potentially infectious material)180106D15dialysis waste (may contain potentially infectious material)180106
14packaging contaminated with hazardous chemicals180106D15packaging contaminated with hazardous chemicals180106
15potentially infectious waste contaminated with chemicals180106D15potentially infectious waste contaminated with chemicals180106
16
Acehole
 
Upvote 0
hello, WE HAVE A WINNER
Thank you very much for your time and much patience, your help is really appreciated. I will heed your advice on the html
thanks again
acehole
 
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
261
Nathan Asius
N
M
  • Question
Formula to populate the next available cell in a column using data populated in another cell.
Replies
4
Views
365
Marshkevuk
M
A
  • Question
How to auto-populate a number entered in one cell to another depending on date range
Replies
9
Views
124
awoohaw
awoohaw
J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
271
Jeffrey Mahoney
Jeffrey Mahoney
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
556
MissDB101
M

Forum statistics

Threads
1,215,326
Messages
6,124,263
Members
449,149
Latest member
mwdbActuary

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