Forum Discussion
Pull data from separate sheet, then stop updating once pulled.
I have to track manpower for my job and we are trying to build a spreadsheet to track day by day changes. The below is a very simplified version of what I use. The yellow cells calculate the number of employees from Site 1 and Site 2. If I delete an employee, the yellow cell updates.
Now I have another sheet. I want the cells in yellow from the first sheet to update to the cells in yellow on the second sheet. But I want the data to pull just on the one date and then not update. Easy enough to do an =Sheet1!(F28), but of course this will continuously update and I am not having luck figuring out how to keep the number the same as the date it was pulled. I figure there has to be a way to tie the update to the date, but still not having much luck with google.
4 Replies
I would go about this very differently. And I'm speaking as a person who for several years was the director of the HR/Payroll database for a major US corporation and had to product monthly headcount reports. I can't imagine doing so on a daily basis, as it appears you're doing, but I think the methodology would still be the same.
The heart of our process was a database that can best be described as transactional: i.e., we recorded each transaction affecting each person. I would assume that somewhere your organization must have something similar. For your purposes (tracking headcount) the most relevant pieces of information would be date of transaction, nature of transaction (Hire, Termination, Job Change, etc), position classification (Full time, Part time, etc), Division, Location, and maybe one or two other characteristics important in tracking headcount.
From such a database, a well designed query could count the number of people currently employed on any given day and do so by classification, location, etc.
Now, we were counting people in the tens of thousands, so there may not be transferability to your situation. But I start by mentioning that by way of background. Your process as described here seems to be picking up the daily number(s) in a very different way, just counting people on the job at two sites and recording a number daily. But it makes me wonder what other data you do track--surely you need to be paying people, and therefore there is some sort of transactional database behind THAT process, if not connected currently to yours. But could it be?
That said, granting that you might not want to change your basic process, is it possible for you to post a copy of the spreadsheets from which you got those images; i.e., post the actual spreadsheet, on OneDrive or GoogleDrive, pasting a link here that grants edit access to that sample. That would help us help you.
- I am always open to suggestions! Unfortunately this is the info I have to work with, and I can't even imagine what it would take to pull information/documentation together from other departments. To be clear, I am not in an HR role, but I do track labor for my region. The spreadsheet I am attaching is an extremely simplified version of what I use. I am actually tracking labor across about 40 sites with roughly 800 employees.
https://docs.google.com/spreadsheets/d/1dD8Nj8_I-jrYOG_hK8mFylP0F2PNunaX/edit?usp=share_link&ouid=100692498345959033872&rtpof=true&sd=trueSee if the attached makes any sense. I've automated a good deal of it, but since I don't know exactly what form your "raw data" comes in, the changes I've made may not work as smoothly as possible. There are a few comments in the spreadsheet, but feel free to come back with any questions.
