Prev: converting email address names in a range of cells to real names
Next: Excel Code
From: John on 19 May 2010 12:45
In my workbook - on sheet1 the abbrev names are in col f - sheet2 has both
full and abbrev names in cols A & B respectively. So I modified your formula
below to and entered it in col G But I get #N/A in col G where I'm trying to
list full names.

Modified formula
=index(sheet2!$A$1:$A$10000,match(f1,$B$1:$B$10000,0))

Orig formula
=index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))

I'm obviously missing something - formulas don't come easy to me.
--
John


"Eduardo" wrote:

> Hi,
> Let's assume you have a list of full names in sheet2 in column A and the
> abbreviate name in column B, then in sheet 1 you have the abbreviate names in
> column A, so in column B enter
>
> =index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))
>
> "John" wrote:
>
> > I'll try this again as I accidentally hit the enter key after typing the
> > subject. In a spreadsheet I have all of my staff listed by their abbreviated
> > names used to identify them on our server. I want to be able to convert
> > there abbreviated names like "gcarls" to "Carlson, Glen". I think one of the
> > If formualtions will work but I'm having difficulty. Can anyone please tell
> > me how to do this?
> > --
> > John
From: Eduardo on 19 May 2010 13:35
Hi John,
if the formula doesn't find the abbreviation will give a #N/A error, maybe
you have blank spaces somewhere in the abreviation, use trim option in
another column

=trim(A1)

then overwritte your column A with this information pasting it as values, do
the same in both sheets

"John" wrote:

> In my workbook - on sheet1 the abbrev names are in col f - sheet2 has both
> full and abbrev names in cols A & B respectively. So I modified your formula
> below to and entered it in col G But I get #N/A in col G where I'm trying to
> list full names.
>
> Modified formula
> =index(sheet2!$A$1:$A$10000,match(f1,$B$1:$B$10000,0))
>
> Orig formula
> =index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))
>
> I'm obviously missing something - formulas don't come easy to me.
> --
> John
>
>
> "Eduardo" wrote:
>
> > Hi,
> > Let's assume you have a list of full names in sheet2 in column A and the
> > abbreviate name in column B, then in sheet 1 you have the abbreviate names in
> > column A, so in column B enter
> >
> > =index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))
> >
> > "John" wrote:
> >
> > > I'll try this again as I accidentally hit the enter key after typing the
> > > subject. In a spreadsheet I have all of my staff listed by their abbreviated
> > > names used to identify them on our server. I want to be able to convert
> > > there abbreviated names like "gcarls" to "Carlson, Glen". I think one of the
> > > If formualtions will work but I'm having difficulty. Can anyone please tell
> > > me how to do this?
> > > --
> > > John
From: John on 19 May 2010 14:00
The message I'm getting says "a value is not available to the formula or
function". My full names column is formatted as Lastname, Firstname but even
when I just entered the Last name (i.e. no spaces or commas) I still got the
message. I adjusted the formula to look at only the range of cells in the
columns that actually contains names or abbreviations and still the same
message. I've also tried formatting the text as number or general and
nothing. Is it ok for thetext to be formatted as text? I also made sure
there were no ' preceeding names or abbrevations.
--
John


"Eduardo" wrote:

> Hi John,
> if the formula doesn't find the abbreviation will give a #N/A error, maybe
> you have blank spaces somewhere in the abreviation, use trim option in
> another column
>
> =trim(A1)
>
> then overwritte your column A with this information pasting it as values, do
> the same in both sheets
>
> "John" wrote:
>
> > In my workbook - on sheet1 the abbrev names are in col f - sheet2 has both
> > full and abbrev names in cols A & B respectively. So I modified your formula
> > below to and entered it in col G But I get #N/A in col G where I'm trying to
> > list full names.
> >
> > Modified formula
> > =index(sheet2!$A$1:$A$10000,match(f1,$B$1:$B$10000,0))
> >
> > Orig formula
> > =index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))
> >
> > I'm obviously missing something - formulas don't come easy to me.
> > --
> > John
> >
> >
> > "Eduardo" wrote:
> >
> > > Hi,
> > > Let's assume you have a list of full names in sheet2 in column A and the
> > > abbreviate name in column B, then in sheet 1 you have the abbreviate names in
> > > column A, so in column B enter
> > >
> > > =index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))
> > >
> > > "John" wrote:
> > >
> > > > I'll try this again as I accidentally hit the enter key after typing the
> > > > subject. In a spreadsheet I have all of my staff listed by their abbreviated
> > > > names used to identify them on our server. I want to be able to convert
> > > > there abbreviated names like "gcarls" to "Carlson, Glen". I think one of the
> > > > If formualtions will work but I'm having difficulty. Can anyone please tell
> > > > me how to do this?
> > > > --
> > > > John
From: Eduardo on 19 May 2010 14:15
Ok, do this, copy your abb name from the list that contains the full name
into the other sheet if the formula works, means that the abb are not exactly
the same

"John" wrote:

> The message I'm getting says "a value is not available to the formula or
> function". My full names column is formatted as Lastname, Firstname but even
> when I just entered the Last name (i.e. no spaces or commas) I still got the
> message. I adjusted the formula to look at only the range of cells in the
> columns that actually contains names or abbreviations and still the same
> message. I've also tried formatting the text as number or general and
> nothing. Is it ok for thetext to be formatted as text? I also made sure
> there were no ' preceeding names or abbrevations.
> --
> John
>
>
> "Eduardo" wrote:
>
> > Hi John,
> > if the formula doesn't find the abbreviation will give a #N/A error, maybe
> > you have blank spaces somewhere in the abreviation, use trim option in
> > another column
> >
> > =trim(A1)
> >
> > then overwritte your column A with this information pasting it as values, do
> > the same in both sheets
> >
> > "John" wrote:
> >
> > > In my workbook - on sheet1 the abbrev names are in col f - sheet2 has both
> > > full and abbrev names in cols A & B respectively. So I modified your formula
> > > below to and entered it in col G But I get #N/A in col G where I'm trying to
> > > list full names.
> > >
> > > Modified formula
> > > =index(sheet2!$A$1:$A$10000,match(f1,$B$1:$B$10000,0))
> > >
> > > Orig formula
> > > =index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))
> > >
> > > I'm obviously missing something - formulas don't come easy to me.
> > > --
> > > John
> > >
> > >
> > > "Eduardo" wrote:
> > >
> > > > Hi,
> > > > Let's assume you have a list of full names in sheet2 in column A and the
> > > > abbreviate name in column B, then in sheet 1 you have the abbreviate names in
> > > > column A, so in column B enter
> > > >
> > > > =index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))
> > > >
> > > > "John" wrote:
> > > >
> > > > > I'll try this again as I accidentally hit the enter key after typing the
> > > > > subject. In a spreadsheet I have all of my staff listed by their abbreviated
> > > > > names used to identify them on our server. I want to be able to convert
> > > > > there abbreviated names like "gcarls" to "Carlson, Glen". I think one of the
> > > > > If formualtions will work but I'm having difficulty. Can anyone please tell
> > > > > me how to do this?
> > > > > --
> > > > > John
First  |  Prev  | 
Pages: 1 2 3
Prev: converting email address names in a range of cells to real names
Next: Excel Code