Formula to record variance and update comments automatically.

Jalal Kasmani

Board Regular
Joined
Feb 14, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Below table has the Hiring requisition movement month on month.

Book1.xlsx
AB
1FebMar
2Req to be raisedReq to be raised
3Sourcing in progressSourcing in progress
4Sourcing in progressOffered
5Sourcing in progressSourcing in progress
6Sourcing in progress#NA
7OfferredSourcing in progress
8OfferredOffered
9Pre-OfferJoined
10Pre-OfferPre-Offer
11Pre-Offer#NA
12Req in ApprovalReq to be raised
13OfferredJoined
14Req to be raised
15Req in Approval
16Pre-Offer
17Req to be raisedReq in Approval
18Sourcing in progressPre-Offer
19Sourcing in progressPre-Offer
20Sourcing in progressPre-Offer
Sheet1


Below are two columns which i update every month manually filtering the each month and recording what moved backward and what moved forward. I am very sure this can be done using a formula.

Book1.xlsx
FGH
21SummarryMovement from FebComments
22Req to be raised+1-1 moved to "Req in Approval", + 1 moved here from "Req in Approval", +1 New RJ raised.
23Req in approval+1+ 1 moved to "Req to be raised", +1 moved here from "Req to be raised", + 1 New RJ raised
24Sourcing in progress-4-1 moved to offered, -3 moved to Pre Offer, -1 Req Cancelled, +1 moved here from "Offered"
25Offered-1-1 moved to "Sourcing in progress", -1 moved to "Joined", +1 moved here from "Sourcing in progress"
Sheet1



Comments column is a critical part. what i do is, i explain the variance count of column "G". I filter on column A on one hiring stage and see what had reduced from that stage(minus), than filter on column B and check what is added to that stage(plus). Please let me know if any doubts
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top