Excel formula help

[adad67]

Hi all,
I am new to this forum and would love some help.
I have a spreadsheet from work I have to fill in loads of data, some cells could be made easier as once I fill in the a column, it means I waould have to fill in a certain word in the c column.
My headings are
[TABLE="width: 1594"]
<tbody>[TR]
[TD="class: xl67, width: 213"]Company[/TD]
[TD="class: xl68, width: 138"]Home[/TD]
[TD="class: xl68, width: 220"]Discipline[/TD]
[TD="class: xl68, width: 595"]Period[/TD]
[TD="class: xl68, width: 163"]Invoice No. [/TD]
[TD="class: xl69, width: 86"]Amount[/TD]
[TD="class: xl70, width: 97"]Capex / PO[/TD]
[TD="class: xl68, width: 82"]Column1[/TD]
[/TR]
</tbody>[/TABLE]
So for eg if company was Zerox I would automatically need "fax" in column c......... if Microsoft was in column a I would need computing in column c etc etc etc
Is there a formula I could use for this?
I have tried lost of ideas from web sites but cannot find one to do this.
Thanks all in advance for any help!
David

 

[mumps]

You would need a list, perhaps in another sheet, that contains the company names in one column and the corresponding words in the adjacent column.

 

[adad67]

You would need a list, perhaps in another sheet, that contains the company names in one column and the corresponding words in the adjacent column.

Thanks for your reply.... could you enlarge please, Im a beginner so would need a step by step walkthrough if possible.
Thanks
David

 

[mumps]

Start by creating a sheet and name it "Data". In column A of this sheet, enter all the company names and in column B, enter the corresponding words for each company. Copy and paste the macro below into the worksheet code module. Do the following: right click the tab name for your other sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a company name in column A and exit the cell. Column C will populate automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim company As Range
    Set company = Sheets("Data").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not company Is Nothing Then
        Target.Offset(0, 2) = company.Offset(0, 1)
    Else
        MsgBox (Target & " not found.")
    End If
    Application.ScreenUpdating = True
End Sub

 

[adad67]

Start by creating a sheet and name it "Data". In column A of this sheet, enter all the company names and in column B, enter the corresponding words for each company. Copy and paste the macro below into the worksheet code module. Do the following: right click the tab name for your other sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a company name in column A and exit the cell. Column C will populate automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim company As Range
    Set company = Sheets("Data").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not company Is Nothing Then
        Target.Offset(0, 2) = company.Offset(0, 1)
    Else
        MsgBox (Target & " not found.")
    End If
    Application.ScreenUpdating = True
End Sub

Thankyou so much... I will have a go at this now!
David

 

[adad67]

Thankyou so much... I will have a go at this now!
David

Thanks Again.,... that works brilliantly.... only prob now is when I save, even as macro enabled file... the code is missing when I restart... any ideas why?
David

 

[mumps]

If you save the workbook as a macro enabled file, the macro should still be there. Remember that this is a Worksheet_Change macro so you won't find it in a standard module. You have to right click the tab name for your sheet and click 'View Code' to see the macro.

 

[adad67]

If you save the workbook as a macro enabled file, the macro should still be there. Remember that this is a Worksheet_Change macro so you won't find it in a standard module. You have to right click the tab name for your sheet and click 'View Code' to see the macro.

HI,
I have done that saved as Macro enabled workbook... but macro is not there when re opening
Dave

 

[mumps]

Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.