Formula and Inserting reference to report to a summary page

Aimee Peyton-Greene

Hello! Thanks for stopping by I am getting a lot of use out of this community page. Thank you all for the help!

Now to business.

I am working on a recruiter tracking sheet. I need to be able to paste into my source sheet and have the sheet talk and push my data to the appropriate reporting spot.

The destination sheet has about 30-40 recruiters and is sorted like below for each month of 2020:

Recruiter Name:

Full Time Employees

Part Time Employees

Transfers

90 Day Drop Offs

I know I will have to do many... many inserts to do this because of the data types (which is fine who needs a weekend anyways).

My data will need to automatically filter and report to the destination sheet sorted by name of the recruiter, month it was reported in, and within each recruiter have the full time employees (0.5 and above) report to the full time row under the appropriate recruiter, and the 0.45 and under report to the Part Time Employees without needing to be manipulated by me or anyone else. My goal is to copy/paste/upload the data into the source sheet and be able to walk away knowing my formula has my back and I am reducing the potential for human error. I have tried inserting the and doing =COUNTIFS statements, but these things get me. I am definitely going to be settling down to do a deep dive into the formulas lessons, multiple times probably, once this settles down.

These are the latest ones I have tried. I tried about 8 other possibilities before this.

=COUNTIFS({Metrics Source Sheet 2020/2021 Range 1}, "Recruiter Name"),(FTE) <0.5), (Hired month= “1”)

=COUNTIFS({Metrics Source Sheet 2020/2021 Range 1}, "Recruiter Name"),(FTE) >=0.5), (Hired month= “1”)

Thank you!

Mark Cronk

@Aimee Peyton-Greene,

Thank you. That's helpful.

Try:

PF External PRN for January =COUNTIFS({Metrics Source Sheet 2020/2021 Range 1}, =PARENT([Primary Column]@row), {INSERT SHEET REFERENCE TO FTE:FTE}, <0.5, {INSERT SHEET REFERENCE TO [Hired month]:[hired month]}, = “1”, {INSERT SHEET REFERENCE TO HIRE DATE COLUMN}, YEAR(@CELL)=2020)

PF External Fills for January =COUNTIFS({Metrics Source Sheet 2020/2021 Range 1}, "Recruiter Name", {INSERT SHEET REFERENCE TO FTE:FTE}, >=0.5, {INSERT SHEET REFERENCE TO [Hired month]:[hired month]}, = “1”, {INSERT SHEET REFERENCE TO HIRE DATE COLUMN}, YEAR(@CELL)=2020)

Where [Primary Column] is the column name of the destination sheet with the Recruiter Name in the Parent row. Using = PARENT([Primary Column]@row]) let's you copy the formulas down to each recruiter without having to enter the recruiters name into the formula.

Since you're working with different years you need to check the year, not just the month. I added that as the last part of the COUNTIFS but I can't tell which of your columns contains the full date. The other option is to create a Hire Year column like you did for Hire Month and search that for 2020.

Any luck with this solution?

Mark

Mark Cronk

Hi Aimee,

Happy to help but I need a little more to go on. Can you upload some screen shots of the grids with column headings and formulas? You're using cross sheet references so it would also help to understand what's on each of the other sheets.

Mark

Aimee Peyton-Greene

The source sheet is organized in columns:

The destination sheet is organized in rows, the parent row is each recruiter's name and within that the data is sorted:

I need it to report to different recruiter names in another sheet based on the month hired, recruiter name, and FTE 0.001-0.49 goes to the PF External PRN count and 0.5-1.0 FTE would go to PF External fills count.

The month columns on the destination sheet are set by year then month number, so 2020-01, 2020-02, 2020-03, etc.

So the destination sheet would have a count by each month of the totals for each employment type for each recruiter- not the raw information provided in the source sheet, just a count for the total.

Mark Cronk

@Aimee Peyton-Greene,

Thank you. That's helpful.

Try:

PF External PRN for January =COUNTIFS({Metrics Source Sheet 2020/2021 Range 1}, =PARENT([Primary Column]@row), {INSERT SHEET REFERENCE TO FTE:FTE}, <0.5, {INSERT SHEET REFERENCE TO [Hired month]:[hired month]}, = “1”, {INSERT SHEET REFERENCE TO HIRE DATE COLUMN}, YEAR(@CELL)=2020)

PF External Fills for January =COUNTIFS({Metrics Source Sheet 2020/2021 Range 1}, "Recruiter Name", {INSERT SHEET REFERENCE TO FTE:FTE}, >=0.5, {INSERT SHEET REFERENCE TO [Hired month]:[hired month]}, = “1”, {INSERT SHEET REFERENCE TO HIRE DATE COLUMN}, YEAR(@CELL)=2020)

Where [Primary Column] is the column name of the destination sheet with the Recruiter Name in the Parent row. Using = PARENT([Primary Column]@row]) let's you copy the formulas down to each recruiter without having to enter the recruiters name into the formula.

Since you're working with different years you need to check the year, not just the month. I added that as the last part of the COUNTIFS but I can't tell which of your columns contains the full date. The other option is to create a Hire Year column like you did for Hire Month and search that for 2020.

Any luck with this solution?

Mark

Aimee Peyton-Greene

The primary column has the name and lists the types of employment, but the parent row has the count of the totals (I already have the formulas for that piece) so it would not be the parent row name but the primary column name, does that make a difference? I keep getting an unparsable notice. I also decided to use a different source sheet for 2021 and renamed the sheet for 2020 data.

Here is the example of what I put. The hired month will be in the column of 2020-01 if we continue to use january for our example on the destination sheet. the hired month will be listed as 1 in the source sheet.

PF External PRN for January

=COUNTIFS({Metrics Source Sheet 2020 Range 1} Range 1}, =PARENT([Recruiter Name Here)@row,{Metrics Source Sheet 2020 Range 1} FTE, <0.5,{Metrics Source Sheet 2020 Range 1} [hired month]} = “1”)