Auto-Correct Formulas ??

  • Thread starter Thread starter cassy01
  • Start date Start date
C

cassy01

i use several computers in my house and and have my own computer set up
so that everytime i write "AB" it comes up with "Athletica Boutique"
using Auto-Correct but i want to know if i can use any formulas so that
when i type any of my shortcuts in coloumn A or B it comes up with the
full Name. Is this possible ??

I dont know but using something like the =IF formula ??

Many Thanks :D
Benn
 
You could set up a table of abbreviations v. long names on another worksheet.

Then you could use =vlookup() to translate:

=if(a1="","",if(iserror(vlookup(a1,sheet2!$a:$b,2,false)),"missing",
vlookup(a1,sheet2!$a:$b,2,false))

and drag down.

If a1 is empty, don't show anything.
if you typed something in A1 that didn't appear in your table, return Missing.
if everything is ok, then return that long name.
 
i dont really understand that so, i have attached a file which has on
sheet1(codes) All the codes in "Column A" so when i type one of those
code on sheet2(Fixtures) it comes up with the full name in the home
column and the ground in the Ground column and also the div. they ae in
so, for example:

if i type "T1" on sheet2 column "B" it replaces it with : "Birds Eye
Juniors" in "B" and it comes up with their ground in Column "D" and
their Div. in Column "A" if that is possible.

If not can you make it so that when i type "T1" it just replaces it
with "Birds Eye Juniors"

Many Thanks:D
Benn

Attachment filename: fixtures.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=395911
 
First, I connect the the MS NewsServers and don't get to see your
attachment--and most people like it that way.

The =vlookup() suggestion wouldn't replace T1 with your phrase. It would only
put your phrase in another cell.

You could have a macro that did update the cell as soon as you typed the
characters and hit enter, though.

I built another sheet (sheet2) and put 4 columns on it:
column A held the abbreviation
column B held the long phrase
column C held the Div (what gets copied to column A)
column D held the Ground (copied to column D)

Then in the worksheet that should have the typing in column B:

right click on the worksheet tab and select View Code
paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myLookupRng As Range
Dim res As Variant

On Error GoTo errHandler:

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("b:b")) Is Nothing Then Exit Sub

With Worksheets("Sheet2")
Set myLookupRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

res = Application.Match(Target.Value, myLookupRng, 0)

If IsError(res) Then
'do nothing--maybe just beep
Beep
Else
Application.EnableEvents = False
Target.Value = myLookupRng(res).Offset(0, 1).Value
Target.Offset(0, -1).Value = myLookupRng(res).Offset(0, 2).Value
Target.Offset(0, 2).Value = myLookupRng(res).Offset(0, 3).Value
End If

errHandler:
Application.EnableEvents = True

End Sub

Then back to excel to try it out.

===========================

But if you're willing to live with another helper column, you can do it all with
worksheet formulas.

Use the same kind of layout for sheet2
but insert a new column A on your input sheet.

Then in the new column C, you can use that vlookup formula to return the long
phrase:
=if(a1="","",if(iserror(vlookup(a1,sheet2!$a:$d,2,false)),"missing",
vlookup(a1,sheet2!$a:$d,2,false))

In the new column B, bring back the division:
=if(a1="","",if(iserror(vlookup(a1,sheet2!$a:$d,3,false)),"missing",
vlookup(a1,sheet2!$a:$d,3,false))

In new column E, bring back the ground:
=if(a1="","",if(iserror(vlookup(a1,sheet2!$a:$d,4,false)),"missing",
vlookup(a1,sheet2!$a:$d,4,false))

(Notice that the formulas are almost identical. Just the column brought back
changes (in two spots).
 
its still not making sence. Is there any chance i can email the file to
you and then you sort it for me ??

Send Email to (e-mail address removed)

Many Thanks
Benn
 
thanks for that but now i want to create another code page but this time
the code is in column A and The Long Name is in Column B.(this sheet is
called REFS)

i want it so that when i type R1 in column E it replaces it with the
long name.
( on another sheet)

if you can just tell me what i have to copy onto the Vlookup page
thing.

Many Thanks :)
Benn
 
thanks for that but now i want to create another code page but this tim
the code is in column A and The Long Name is in Column B.(this sheet i
called REFS)

i want it so that when i type R1 in column E it replaces it with th
long name.
( on another sheet)

if you can just tell me what i have to copy onto the Vlookup pag
thing.

Many Thanks :)
Ben
 
thanks for that but now i want to create another code page but this tim
the code is in column A and The Long Name is in Column B.(this sheet i
called REFS)

i want it so that when i type R1 in column E it replaces it with th
long name.
( on another sheet)

if you can just tell me what i have to copy onto the Vlookup pag
thing.

Many Thanks
Ben
 
Back
Top