NAME function working in reverse??

[F. Lawrence Kulchar]

I have a List of NFL teams in a series of rows that I wish to convert to
their respective abbreviations:

For Example, In Column A, rows 1 -36, I have all the NFL teams as follows:

A

1 Kansas City
2. Oakland
3. Houston
4. Cincinnati
5 Arizona
6. Miami
7. Tampa Bay
8. Carolina
9. St. Louis
10. Detroit
11. New Orleans
12. Pittsburgh
13. Jacksonville
14. Cleveland
15. Green Bay

etc, etc, down to Row 37 for all 36 teams. I have used the INSERT Menu,
Define NAMES, so that:

Names in workbook: Kansas City
= KC
and,
Oaklank
= OAK

and so on.....

So when I type in cell B1, "=Kansas City"...B1 becomes KC
When I type in cell B2, "=Oakland"...B2 becomes OAK

THIS TAKES A LOT OF TYPING TO CONVERT THE NFL NAMED TEAMS (AS IN COLUMN A),
TO THEIR RESPECTIVE ABBREVIATIONS...

If I try typing in cell B1 "=A1", I get AGAIN "Kansas City"...NOT "KC" as I
would like to see!

Can anyone help me out their to convert the NFL 'long names' which I paste
from a website to their respective abbreviations.

I do use a VLOOKUP routine to later, on a different spreadsheet, search for
the needed abbreviations; ergo, the reason I require a 'short way' to convert
a column of lomg NFL teams, such as "Chicago Bears" to "CHI"...!

Please help.
Thanks,

The method I am using requires typing 36 times, into 36 cells, long entries
such as..."= Baltimore", or "=Indiannapolis"...all of which is too time
consuming.


Thanks again,

FLKulchar

 

[Sheeloo]

I am not sure what you really want or why you are doing it the way you are
doing;

I tried the following;

1. Enter the data at the end of this post in Col A to D
2. Selected Col A & B
Chose Insert->Name->Create->Left Column

This gave me abbreviated name based on the formula in Col D when it looked
at the values in Col C.

PL. NOTE that when name is created from 'Arizona Cardinals' it is created as
'Arizona_Cardinals'... hence the need for column C

Here is the data;
Arizona Cardinals ARI Arizona_Cardinals =INDIRECT(C1)
Atlanta Falcons ATL Atlanta_Falcons =INDIRECT(C2)
Baltimore Ravens BAL Baltimore_Ravens =INDIRECT(C3)
Buffalo Bills BUF Buffalo_Bills =INDIRECT(C4)
Carolina Panthers CAR Carolina_Panthers =INDIRECT(C5)
Chicago Bears CHI Chicago_Bears =INDIRECT(C6)
Cincinnati Bengals CIN Cincinnati_Bengals =INDIRECT(C7)
Cleveland Browns CLE Cleveland_Browns =INDIRECT(C8)
Dallas Cowboys DAL Dallas_Cowboys =INDIRECT(C9)
Denver Broncos DEN Denver_Broncos =INDIRECT(C10)
Detroit Lions DET Detroit_Lions =INDIRECT(C11)
Green Bay Packers GB Green_Bay_Packers =INDIRECT(C12)
Houston Texans HOU Houston_Texans =INDIRECT(C13)
Indianapolis Colts IND Indianapolis_Colts =INDIRECT(C14)
Jacksonville Jaguars JAX Jacksonville_Jaguars =INDIRECT(C15)
Kansas City Chiefs KC Kansas_City_Chiefs =INDIRECT(C16)
Miami Dolphins MIA Miami_Dolphins =INDIRECT(C17)
Minnesota Vikings MIN Minnesota_Vikings =INDIRECT(C18)
New England Patriots NE New_England_Patriots =INDIRECT(C19)
New Orleans Saints NO New_Orleans_Saints =INDIRECT(C20)
New York Giants NYG New_York_Giants =INDIRECT(C21)
New York Jets NYJ New_York_Jets =INDIRECT(C22)
Oakland Raiders OAK Oakland_Raiders =INDIRECT(C23)
Philadelphia Eagles PHI Philadelphia_Eagles =INDIRECT(C24)
Pittsburgh Steelers PIT Pittsburgh_Steelers =INDIRECT(C25)
San Diego Chargers SD San_Diego_Chargers =INDIRECT(C26)
Seattle Seahawks SEA Seattle_Seahawks =INDIRECT(C27)
San Francisco 49ers SF San_Francisco_49ers =INDIRECT(C28)
Saint Louis Rams STL Saint_Louis_Rams =INDIRECT(C29)
Tampa Bay Buccaneers TB Tampa_Bay_Buccaneers =INDIRECT(C30)
Tennessee Titans TEN Tennessee_Titans =INDIRECT(C31)
Washington Redskins WAS Washington_Redskins =INDIRECT(C32)