populate next empty cell

Please help me this...... how to make excel to found the next empty cell and to put a number there from a list of data. The attached file is a sample of what I want to do: It is a "Perfect Attendance" spreadsheet. I have 3 tables: Table 1 (green) is the data. Table 2 is the entry of the employees who called off (Yellow). Table 3 is the result I am looking for (red). I need excel to put the names (or employees #) of the people who have perfect attandance. I can not use codes, macros or pivot table. I have to use stright formulas. Thanks a lot!!!

Hi royUK, Thanks for your help! This is the scoop: My HR wants to have a list with employees who have Perfect Attendance for 1 full quarter. My 1st Table (sheet) is the data (approx 700 employees-names and payroll #). My second table (sheet) is the entry. Every day I will enter the employee number of the people who called off (I will use Vlookup to get the names from my data sheet). That way I will generate a list of names of the employees who do not have a perfect attandance. From sheet 1 and sheet 2 I want excel to generate sheet 3 (table 3) with the names of the employees who have a perfect attendance. My HR is interested in a list of perfect attendance only. They are not going to give any Ferraris, but I want them to recognize the people who show up for work every day. THANK A LOT!!

There are a couple or 3 ways to do this. The obvious one (and the one I'm sure you've worked out) is to use a couple of functions, but these leave the gaps - hence the title of your thread.

BTW: the formulas I used to work out perfect attendees, but left gaps were:
In G4 I had: =IF(ISERROR(MATCH(A4,$D$4:$D$14,0)),A4,"")
...copied down to G14
In H4 I had: =IF(G4="","",VLOOKUP(G4,DATA,2,FALSE))
...copied down to H14

An alternative is to write a macro. Now I know in your initial message that you don't want to use macros, but to get rid of the gaps is going to be a pretty major undertaking to find a formula (though there are some pretty smart people on this forum so you might get lucky).

in the meantime, here is a macro which will do what you're after:

Please Login or Register  to view this content.

Thank you RoyUK! You are correct! I will use the data sheet and will asign a point to a name when he/she calls off. At the end of the quarter I will sort/filter the sheet and all the names with perfect attandance will come at the top because there is no any number asigned to their names. Simple is the way to go with this. Thanks again!