Name Identification and Format from different reports

[lyonstl03]

The work book has three work sheets.
I have two separate worksheets (1 and 2) that are reports that provide employee names two different ways. Last, First and First Last. My issue comes when I have to compile numbers per employee into one worksheet (3) based on different metrics. Is there a way for worksheet 3 to see the data by name no matter the name format.

Ex - This is Sheet 3 which is what I'm trying to do to compile information from both reports to get percentages, errors, etc. It's just the name thing is messing with me.

Smith, John (C2)

Apr-19 (D2)

77 (Sheet1 COUNTIFS here)

0 (Sheet2 COUNTIFS here)​

0​

N/A

0​

0​

N/A

=COUNTIFS('Sheet1'!A:A,$D2,'Sheet1'!$B:$B,$C2)

=COUNTIFS('Sheet2'!A:A,$D2,'Sheet2'!$E:$E,$C2,'Sheet2'!B:B, "Reg Approved")

Sheet1 Report

4/1/2019​

Smith, John $B

Approved

02/06/2013

07/02/2018

03/03/2013

No

No

1234

No

1234567

1234567

Open

0

Program

Sheet2 Report

4/1/2019​

Reg Approved

Data

Data

John Smith $E

Customer Name

12345678

4/17/2020

4/21/2020

NO

4/22/2020

5

N

Data

So, the names in Sheet1 are in column B and are formatted (Last, First) and the names in sheet two are in column E and formatted (First Last).

Is there a solution without changing the names on these reports?

 

[lyonstl03]

I tried using the following :

=MID(B2&" "&B2,FIND(", ",B2)+1,LEN(B2))


I put that formula in column P of one of the reports which automatically switches all the names to First Last, but when I change my Sheet1 formula to grab the name from P then it isn't recognizing the name in the report.

 

[lyonstl03]

I've tried finding some more information about how to recognize the value or text of a cell instead of the formula itself. I'm still stumped.

 

[lyonstl03]

I believe I've solved this issue using TRIM.

=TRIM(MID(B2&" "&B2,FIND(", ",B2)+1,LEN(B2)))

That now picks up the actual data in the COUNTIFS formula instead of the formula that was used to switch the names.