IF A2 = "Blank" then copy information to another sheet

[Netotigr]

I was asked if I could help out the Payroll dept with this.

They have an Excel Sheet that they use at the start of the day to get a headcount of employees for that night.

The sheet looks like this

On-Site EMPLOYEE NAMES CRAFT FOREMAN
1 ADAMS, DARRON L. PIPEFITTERS (A-C) ADAMS, DARRON LEVON
BOURGEOIS, JARED M. PIPEFITTERS (A-C) BILLY, BOB

when an employee is Absent, they delete the 1 under "On-Site"

I want to copy the information to the right for every employee that is Absent for that day and to be added to a different Sheet that is used as an Absent Log

this is done every day

so something like

if A2-->A100 = "blank" then copy B2/C2/D2 to this sheet

[hemesh]

Can you attach a sample book. GO to advanced, manage attachment, select files, upload files and hit done

[Netotigr]

if anything on B line = blank then copy C/D/E/F to another workbook into cells A/B/C/D and write "ABSENT" on E

now this will be done everyday

[AlKey]

Hi

Please see attached file which contains formula.
This is an array formula and must be entered by holding Ctrl+Shift and then press Enter.

[Netotigr]

ok this is exactly what I want, but when I try to use the same formula on the actual sheet that I use only the 1st name gets copied over after that nothing

and I get

#NUM! error..


maybe I should just upload the sheet that I use? I'm sorry, I'm very new to this

[vlady]

@ Netotigr just remember to desensitize the data, e.g. you can change the name like A, B C..

[Netotigr]

This is the actual file that is being used.

At the start of the shift an employee will delete the 1 from the B column from 2 sheets "GCU" & "Gasifier"

The employee that has a "blank" is ABSENT , this sheet is currently used every day to get a break down of exactly how many employees of each craft are Absent for that day
if you go to the "TOTALS" tab you'll see what I'm talking about.

I need help adding these employees that are ABSENT added to the sheet "GCU Absent" & Gasifier Absent" as it is 2 different projects.

The problem that I see is this


Say 10 employees are absent and added to the sheet, the following day those 10 employees are NOT absent... how can I make them stay on that list?

[AlKey]

ok this is exactly what I want, but when I try to use the same formula on the actual sheet that I use only the 1st name gets copied over after that nothing

and I get

#NUM! error..


maybe I should just upload the sheet that I use? I'm sorry, I'm very new to this

This an array formula and you have to enter this formula with Ctrl+Shift+Enter. Then drag formula across and down.

[Netotigr]

no idea what an Array formula is, guess I have to learn that first thanks anyway

[AlKey]

no idea what an Array formula is, guess I have to learn that first thanks anyway

Don't get stressed Just follow instructions below:

1.Click on the where you have your formula and press F2 on your upper row keyboard.
2. Now hold down Ctrl on the bottom left of your keyboard and without releasing it press Shift and now hit ENTER. You should see {} around the formula. If you see them, you entered formula right.

[Netotigr]

ok thanks guys, got it working... now here's the last question


All the employees that where Absent today where added using the formula , everything is perfect.
Tomorrow everybody will have a 1 by their name aka not Absent, so if the formula keeps working it will delete the employees it added today, hope it makes sense

so what can I do to keep the names of the employees added but for those cells to ignore the formula and just keep the info on them

[AlKey]

I think you are talking about preserving older records? What you can do copy these rows and paste on another tab without formula.
Copy, Paste Special Values and Formats. That way these records will not change.
Hope this help.

Also, please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[Netotigr]

done, yes thank you all will rep