first name last name
 

I have a spreadsheet that is used by two different departments. Sales uses
it to report commissions and lists names by first name then last name. HR
uses it to be able to imput commissions into payroll but thier sheet needs to
be listed lastname, first name. I have the workbook setup so the toal sales
commissions are pulled from the Sales sheet and dumped into the HR sheet
suing the forumla:

=SUMIF('Sales'!$A$8:$A$500,A3,'HR'!$D$8:$D$500)

where 'Sales'!$a$8:$a$500 is where the name is referenced on the sales sheet
and A3 is where the name is referenced on the HR sheet in reverse. Is there
a way I can get Excel to recognize the name in reverse so this formula will
work?

first name last name
 

=SUMIF($A$8:$A$500,MID(A3& " " & A3,FIND(",",A3)+2,LEN(A3)-1),$D$8:$D$500)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"brodiemac" wrote in message
...
I have a spreadsheet that is used by two different departments. Sales
uses
it to report commissions and lists names by first name then last name. HR
uses it to be able to imput commissions into payroll but thier sheet needs
to
be listed lastname, first name. I have the workbook setup so the toal
sales
commissions are pulled from the Sales sheet and dumped into the HR sheet
suing the forumla:

=SUMIF('Sales'!$A$8:$A$500,A3,'HR'!$D$8:$D$500)

where 'Sales'!$a$8:$a$500 is where the name is referenced on the sales
sheet
and A3 is where the name is referenced on the HR sheet in reverse. Is
there
a way I can get Excel to recognize the name in reverse so this formula
will
work?

first name last name
 

You, sir, ARE A GENIUS!!!! Thank you very much!

"Bob Phillips" wrote:

=SUMIF($A$8:$A$500,MID(A3& " " & A3,FIND(",",A3)+2,LEN(A3)-1),$D$8:$D$500)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"brodiemac" wrote in message
...
I have a spreadsheet that is used by two different departments. Sales
uses
it to report commissions and lists names by first name then last name. HR
uses it to be able to imput commissions into payroll but thier sheet needs
to
be listed lastname, first name. I have the workbook setup so the toal
sales
commissions are pulled from the Sales sheet and dumped into the HR sheet
suing the forumla:

=SUMIF('Sales'!$A$8:$A$500,A3,'HR'!$D$8:$D$500)

where 'Sales'!$a$8:$a$500 is where the name is referenced on the sales
sheet
and A3 is where the name is referenced on the HR sheet in reverse. Is
there
a way I can get Excel to recognize the name in reverse so this formula
will
work?

first name last name
 

I'm going to try to figure this out on my own but can you tell me how to do
this in reverse?

"Bob Phillips" wrote:

=SUMIF($A$8:$A$500,MID(A3& " " & A3,FIND(",",A3)+2,LEN(A3)-1),$D$8:$D$500)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"brodiemac" wrote in message
...
I have a spreadsheet that is used by two different departments. Sales
uses
it to report commissions and lists names by first name then last name. HR
uses it to be able to imput commissions into payroll but thier sheet needs
to
be listed lastname, first name. I have the workbook setup so the toal
sales
commissions are pulled from the Sales sheet and dumped into the HR sheet
suing the forumla:

=SUMIF('Sales'!$A$8:$A$500,A3,'HR'!$D$8:$D$500)

where 'Sales'!$a$8:$a$500 is where the name is referenced on the sales
sheet
and A3 is where the name is referenced on the HR sheet in reverse. Is
there
a way I can get Excel to recognize the name in reverse so this formula
will
work?

first name last name
 

Here you are

=SUMIF($A$8:$A$500,MID(A3&", "&A3,FIND(" ",A3)+1,LEN(A3)+1),$D$8:$D$500)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"brodiemac" wrote in message
...
I'm going to try to figure this out on my own but can you tell me how to
do
this in reverse?

"Bob Phillips" wrote:

=SUMIF($A$8:$A$500,MID(A3& " " &
A3,FIND(",",A3)+2,LEN(A3)-1),$D$8:$D$500)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"brodiemac" wrote in message
...
I have a spreadsheet that is used by two different departments. Sales
uses
it to report commissions and lists names by first name then last name.
HR
uses it to be able to imput commissions into payroll but thier sheet
needs
to
be listed lastname, first name. I have the workbook setup so the toal
sales
commissions are pulled from the Sales sheet and dumped into the HR
sheet
suing the forumla:

=SUMIF('Sales'!$A$8:$A$500,A3,'HR'!$D$8:$D$500)

where 'Sales'!$a$8:$a$500 is where the name is referenced on the sales
sheet
and A3 is where the name is referenced on the HR sheet in reverse. Is
there
a way I can get Excel to recognize the name in reverse so this formula
will
work?