COUNTIF not working when referencing a table on another sheet

[deweywsu]

Hi.

I am trying to make a dashboard for hiring and departing employees. I have two simple tables on two worksheets respectively. I'm trying to compare the two tables to determine who was hired and who quit. The input tables are made from the list of who is currently employed, which I receive every week. Some names will disappear (these left the company), and some will appear (these were just hired).

On a third sheet, I have a column of formulas that attempts to reference the two 'input' tables with COUNTIF statements. The weird thing is, one of the references works, the other one doesn't give the right results, and I can't figure out why (see the third image below titled "results"). I tried switching the cell format of the two 'input' tables to text and back to general, but no luck. Can anyone help?

If, however, I COPY the results of the two input tables to the same worksheet as the COUNTIF statements, they both work fine (see the last photo with new tables "18" and "29"). I was under the assumption I could reference a table within a formula by name and not have to use the sheet name. Is that wrong? It seems like not, since the first table can be referenced from another sheet just fine.

Most importantly, if there is a better/slicker way to do this, I'd love to know.


Thank you for any help!
-Josh

 

[ExceLoki]

are you only comparing this from one week to another or have a running record for weeks/months/years?

 

[deweywsu]

are you only comparing this from one week to another or have a running record for weeks/months/years?

Thanks for your reply. Just from one week to the other.

 

[ExceLoki]

the first one is showing who left, the second is showing who was hired, the third combines them. let me know if this works for you.
-----------

count if hired or fired.xlsx
	A	B	C
1	Emplip	Full_Name
2	12345	John A	go away
3	12346	Bill T	stayed
4	12347	Lacey K	stayed
5	12348	Bill B	stayed
6	12349	John T	stayed
7	12350	Ron S	stayed
8	12351	Will S	stayed
9	12352	Jim H	stayed
10	12353	Dana D	stayed
11	12354	Lindsey G	go away
12	12355	Frank T	stayed
13	12356	Paul D	stayed
14	12357	Jill R	stayed
15	12358	Manny T	stayed
16	123490	sam b	stayed
17	qwe	qwe	stayed
wk1

Cell Formulas
Range	Formula
C2:C17	C2	=IF(COUNTIF(Week2[[#All],[Full_Name]],Week1[Full_Name])=0,"go away","stayed")
Dynamic array formulas.

-----------

count if hired or fired.xlsx
	A	B	C
1	Emplip	Full_Name
2	12346	Bill T	stayed
3	12347	Lacey K	stayed
4	12348	Bill B	stayed
5	12349	John T	stayed
6	12350	Ron S	stayed
7	12351	Will S	stayed
8	12352	Jim H	stayed
9	12353	Dana D	stayed
10	12355	Frank T	stayed
11	12356	Paul D	stayed
12	12357	Jill R	stayed
13	12358	Manny T	stayed
14	12359	Lenny K	hired
15	12360	Paul S	hired
16	123490	sam b	stayed
17	qwe	qwe	stayed
wk2

Cell Formulas
Range	Formula
C2:C17	C2	=IF(COUNTIF(Week1[Full_Name],Week2[Full_Name])=0,"hired","stayed")
Dynamic array formulas.

-----------

count if hired or fired.xlsx
	A	B	C
1	Emplip	Full_Name	wk1
2	12345	John A	go away
3	12346	Bill T	stayed
4	12347	Lacey K	stayed
5	12348	Bill B	stayed
6	12349	John T	stayed
7	12350	Ron S	stayed
8	12351	Will S	stayed
9	12352	Jim H	stayed
10	12353	Dana D	stayed
11	12354	Lindsey G	go away
12	12355	Frank T	stayed
13	12356	Paul D	stayed
14	12357	Jill R	stayed
15	12358	Manny T	stayed
16	123490	sam b	stayed
17	qwe	qwe	stayed
18	12359	Lenny K	hired
19	12360	Paul S	hired
status

Cell Formulas
Range	Formula
C2:C19	C2	=IFERROR(VLOOKUP([@Emplip],'wk1'!A2:C17,3,FALSE),"hired")

Named Ranges
Name	Refers To	Cells
'wk2 (2)'!wk_1	=Week1[Emplip]	C2
wk_1	=Week1[Emplip]	C2